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.

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)

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

Answer 2

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.

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.