The ORDER BY clause is invalid in views, inline functions, derived tables

Question

Error message

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Code:

SELECT * 
FROM 
    (SELECT 
         t.[Statement_ID], t.[InvoiceID], 
         t.S_Type as Type, t.Description, t.Date, 
         t.Debit, t.Credit, b.Balance 
     FROM 
         Statement as t 
     CROSS apply 
         (SELECT Balance = SUM(Debit) - SUM(Credit) 
          FROM Statement as x 
          WHERE (x.date < t.date or x.date = t.date and x.[Statement_ID] <= t.[Statement_ID] ) 
            AND x.CustID = t.CustID ) b 
     WHERE  
          t.CustID = '48' 
          AND date between '2015-01-01' AND '2016-01-01' 
     ORDER BY
         t.date) 
ORDER BY
    InvoiceID, Statement_ID

And what is the problem? The error message says exactly what is wrong. — Allan S. Hansen, Jan 07 '16 at 11:21
remove `order by t.date` https://msdn.microsoft.com/en-us/library/ms188385.aspx — Ric, Jan 07 '16 at 11:23
@AllanS.Hansen - Whilst that may shut up the compiler, it doesn't achieve anything useful, except possibly causing the server to waste some resources — Damien_The_Unbeliever, Jan 07 '16 at 11:33
@Damien_The_Unbeliever but it would still shut up the compiler ;) — Allan S. Hansen, Jan 07 '16 at 11:34
I think dasblinkenlight's answer was mostly correct, had they not chosen to use `inner`, a keyword, as the alias. — Damien_The_Unbeliever, Jan 07 '16 at 11:48
Especially in SQL Server, [don't use `BETWEEN` with date/time/timestamp types](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). Use an exclusive upper-bound (`<`) instead. For instance, I find a range that is "a month and a day" to be unusual (usually you only want **1 month**). — Clockwork-Muse, Jan 07 '16 at 14:10
Also `AND date between '2015-01-01' AND '2016-01-01'` I think it is more precise like `AND ( ( '2015-01-01' <= date ) and ( date < '2016-01-01') )` — shadow, Jan 07 '16 at 14:40

Petrik · Accepted Answer · 2016-01-07T13:52:38.690

0

Move first ORDER BY to the end:

SELECT * 
FROM 
    (SELECT 
         t.[Statement_ID], t.[InvoiceID], 
         t.S_Type as Type, t.Description, t.Date, 
         t.Debit, t.Credit, b.Balance 
     FROM 
         Statement as t 
     CROSS apply 
         (SELECT Balance = SUM(Debit) - SUM(Credit) 
          FROM Statement as x 
          WHERE (x.date < t.date or x.date = t.date and x.[Statement_ID] <= t.[Statement_ID] ) 
            AND x.CustID = t.CustID ) b 
     WHERE  
          t.CustID = '48' 
          AND date >= '2015-01-01' AND date <= '2016-01-01' 
         ) x
ORDER BY
    Date, InvoiceID, Statement_ID

edited Jan 07 '16 at 13:52

answered Jan 07 '16 at 13:17

Petrik

823
2
12
25

1

You need an alias for a subquery in `FROM`. This will currently error with "incorrect syntax near `ORDER BY`..." or words to that effect. `t` would be a suitable one and would make the reference to `t` in the `ORDER BY` valid again also (there's no `t` alias visible in the outer query, currently). – Damien_The_Unbeliever Jan 07 '16 at 13:37
Cough. You've made half of the correction I suggested. Re-read the third sentence in the previous comment. `t.date` is still going to cause issues as this currently stands. – Damien_The_Unbeliever Jan 07 '16 at 13:48
Sorry, I haven't realised that it is refering to alias, which obviously won't be valid in outer query. Now the ORDER BY is using the column name, so will work fine. I have also changed the BETWEEN to >= AND <= (Please read: https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common ) – Petrik Jan 07 '16 at 13:54
Also if Date is in Datetime format, please explicitly convert or cast this to DATE datatype – Petrik Jan 07 '16 at 13:55
1

thanks Petrik for the solution – Ayman Jan 07 '16 at 14:33

The ORDER BY clause is invalid in views, inline functions, derived tables

1 Answers1

Linked