1

I need next SQL query:

SELECT [t1].[monthp], [t1].[yearp], [t1].[coeftarif]
FROM [dbo].[sal_hightariff] AS [t0]
INNER JOIN [dbo].[sal_tarifstav] AS [t1] ON ((
    SELECT TOP (1) [t3].[code_no]
    FROM [dbo].[sal_tarifstav] AS [t3]
    WHERE ([t3].[codetnum] = @codetnum) AND ([t3].[yearp] = [t0].[yearp]) AND ([t3].[monthp] = [t0].[monthp])
    ORDER BY [t3].[datestart]
    )) = [t1].[code_no]
WHERE [t0].[yearp] * 12 + [t0].[monthp] >= @value1 AND [t0].[yearp] * 12 + [t0].[monthp] <= value2

So, I write this code:

var coeflist = from high in dbContext.sal_hightariff
               join stav in dbContext.sal_tarifstav on (from tarifstav in dbContext.sal_tarifstav
                                                        where tarifstav.codetnum == vacationcodetnum
                                                            && tarifstav.yearp == high.yearp
                                                            && tarifstav.monthp == high.monthp
                                                        orderby tarifstav.datestart
                                                        select tarifstav.code_no).FirstOrDefault() equals stav.code_no
               where high.yearp * 12 + high.monthp >= minday.Year * 12 + minday.Month
                    && high.yearp * 12 + high.monthp <= vacationdatestart.Year
                        * 12 + vacationdatestart.Month
               select new
               {
                   stav.monthp,
                   stav.yearp,
                   stav.coeftarif
               };

But my code generates:

SELECT [t1].[monthp], [t1].[yearp], [t1].[coeftarif]
FROM [dbo].[sal_hightariff] AS [t0]
INNER JOIN [dbo].[sal_tarifstav] AS [t1] ON ((
    SELECT TOP (1) [t3].[code_no]
    FROM (
        SELECT [t2].[code_no], [t2].[codetnum], [t2].[yearp], [t2].[monthp]
        FROM [dbo].[sal_tarifstav] AS [t2]
        ORDER BY [t2].[datestart]
        ) AS [t3]
    WHERE ([t3].[codetnum] = @p0) AND ([t3].[yearp] = [t0].[yearp]) AND ([t3].[monthp] = [t0].[monthp])
    )) = [t1].[code_no]
    WHERE ((((CONVERT(Int,[t0].[yearp])) * @p1) + (CONVERT(Int,[t0].[monthp]))) >= @p2)
        AND ((((CONVERT(Int,[t0].[yearp])) * @p3) + (CONVERT(Int,[t0].[monthp]))) <= @p4)

In which there is an error:

Msg 1033, Level 15, State 1, Line 13 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

So, if I use methods FirstorDefault() or Take(1) in subquery, LINQ generates SELECT TOP (1) FROM SELECT ... But I need TOP (1) inside subquery to use ORDER BY statement.

Menuka Ishan
  • 5,164
  • 3
  • 50
  • 66
AxelW7
  • 11
  • 1
  • What about `Take(int.MaxValue)` ? – leppie Nov 30 '11 at 06:59
  • @leppie I need just 1 row and TOP (1) statement should by by inside scope with ORDER BY statement – AxelW7 Nov 30 '11 at 07:10
  • See my answer on **[SQL Server Query Error -ORDER BY clause is invalid in views](https://stackoverflow.com/questions/36697511/sql-server-query-error-order-by-clause-is-invalid-in-views/61702593#61702593)**. – Murat Yıldız May 09 '20 at 19:35

0 Answers0