ORDER BY clause is invalid unless TOP or FOR XML is also specified

Question

I get "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." error with the following code. I initially had two tables, ADSAREAS & CATEGORIES. I started receiving this error when I removed CATEGORIES table.

    Select Case SIDX  
     Case "ID" : SQLCONT1 = " AdsAreasID"
     Case "Page" : SQLCONT1 = " AdsAreasName"
     Case Else : SQLCONT1 = " AdsAreasID"  
End Select   
Select Case SORD  
     Case "asc" : SQLCONT2 = " ASC"
     Case "desc" : SQLCONT2 = " DESC"
     Case Else : SQLCONT2 = " ASC"  
End Select   
''# search feature --->
Select Case SEARCHFIELD  
     Case "ID" : SQLSFIELD = "AND AdsAreasID"
     Case "Ads Areas" : SQLSFIELD = "AND AdsAreasName"
     Case Else : SQLSFIELD = ""  
End Select  
Select Case SEARCHOPER  
     Case "eq" : SQLSOPER = " = " & SEARCHSTRING
     Case "ne" : SQLSOPER = " <> " & SEARCHSTRING
     Case "lt" : SQLSOPER = " <" & SEARCHSTRING
     Case "le" : SQLSOPER = " <= " & SEARCHSTRING    
     Case "gt" : SQLSOPER = " >" & SEARCHSTRING
     Case "ge" : SQLSOPER = " >= " & SEARCHSTRING
     Case "bw" : SQLSOPER = " LIKE '" & SEARCHSTRING & "%' "
     Case "ew" : SQLSOPER = " LIKE '%" & SEARCHSTRING & "' "
     Case "cn" : SQLSOPER = " LIKE '%" & SEARCHSTRING & "%' "
     Case Else : SQLSOPER = ""  
End Select  
''# search feature --->

SQL = "SELECT * FROM ( SELECT A.AdsAreasID, A.AdsAreasName, ROW_NUMBER() OVER (ORDER BY A.AdsAreasID) As Row"
SQL = SQL & " FROM ADSAREAS A"
SQL = SQL & " WHERE Row > ("& RecordsPageSize - RecordsPerPage &") AND Row <= ("& RecordsPageSize &") ORDER BY" & SQLCONT1 & SQLCONT2
Set objXML = objConn.Execute(SQL)

Enterogastrone

Answer 1

You moved the ORDER BY clause to the inner query in rewriting it. Add a parentheses (and identifier) after the WHERE clause so that ORDER BY applies to the outer SELECT instead.

SQL = "SELECT * FROM ( SELECT A.AdsAreasID, A.AdsAreasName, ROW_NUMBER() OVER (ORDER BY A.AdsAreasID) As Row"
SQL = SQL & " FROM ADSAREAS A"
SQL = SQL & " WHERE Row > ("& RecordsPageSize - RecordsPerPage &") AND Row <= ("& RecordsPageSize &")) inner ORDER BY" & SQLCONT1 & SQLCONT2

Sensate

I must have gone blind. Thank you so much! – Enterogastrone

I was blind before I met you – Derris

Answer 2

Alternative to accepted answer you can simply use TOP (100) PERCENT

for example:

SELECT table1Col, ...
FROM   yourTABLE1
JOIN   -- doesn't matter what join you use
( SELECT TOP (100) PERCENT
         table2Col, ...
  FROM   yourTABLE2
  ORDER BY table2Col,....
) AS TB2 ON yourTABLE1.Col = TB2.Col 

now your ORDER will work

Parachute

Answer 3

It would help if you looked at the query that's being generated by the code:

SELECT * 
FROM (
  SELECT 
    A.AdsAreasID, 
    A.AdsAreasName, 
    ROW_NUMBER() OVER (ORDER BY A.AdsAreasID) As Row
  FROM ADSAREAS A
  WHERE 
    Row > ("& RecordsPageSize - RecordsPerPage &") 
    AND Row <= ("& RecordsPageSize &")
  ORDER BY" & SQLCONT1 & SQLCONT2

Notice that you're missing a closing ) character, and that if one were placed on the end, the ORDER BY would be inside the inner query rather than the outer one, which is invalid. You're better off removing the outer query entirely. It serves no purpose.

This is all notwithstanding the SQL injection concerns with building queries this way.

Forethoughtful

Answer 4

In addition to above accepted answers, from the book Inside Microsoft SQL Server 2008: T-SQL Programming, chapter 1 page 4 it says:

Notice that the error doesnt say that ORDER BY is disallowed altogether; rather, it indicates a couple of exceptions where it is allowed - when TOP or FOR XML is also specified. Remember that both TOP and FOR XML are T-SQL extensions, not standard SQL elements. TOP and ORDER BY or ORDER BY and FOR XML are part of the result set specification, where as ORDER BY alone is not, and only specifies a detail of presentation. Hence, TOP and ORDER BY or ORDER BY and FOR XML are allowed in a view definition, where ORDER BY alone is not.

Incept

Answer 5

It looks like the problem is that you are trying to apply order by clause to the inner select statement which is prohibited. You should apply order by clause only to the outermost select statement.

Floydflss

Answer 6

This maybe useful to someone who is in the same case as I am trying to order self related table and I was using two inner selection instead of one select and getting the same error you mentioned.

SELECT 
* into tmpToUatOrdered FROM tmpToUatScript 

ORDER BY MAX(CASE WHEN parent_id IS NULL THEN UPDATED_DATE END) 
  OVER (PARTITION BY COALESCE(parent_id, id)) DESC,
  parent_id, UPDATED_DATE DESC 

Zrike