CODEWITHSUNDEEP
sqlsql-serversql-order-by
Mohamed Nuur
Mohamed Nuur

Reputation: 5645

How to fix incorrect syntax near keyword 'ORDER'

I've got the following SQL and it's giving me Incorrect syntax near keyword 'ORDER' when I try to execute it:

SELECT COUNT(*) AS ID 
FROM Employees i
  INNER JOIN #WeightedIDs w
  ON (i.ID = w.ID)
WHERE (i.DepartmentID = 10 and i.ShiftID = 2)

UNION ALL

SELECT i.ID FROM Employees i
  INNER JOIN #WeightedIDs w
  ON (i.ID = w.ID)
WHERE (i.DepartmentID = 10 and i.ShiftID = 2)
ORDER BY w.[Weight] ASC

How should I fix it?

UPDATE:

Here's what I'm actually trying to accomplish. I have a SPROC that is generating the query dynamically based on a bunch of different conditions. It also builds a temp table that contains ID's and weights associated with those id's so I would like the results sorted by that. Once the query is generated, I would like to get the count as well as the id's found returned in a list.

Upvotes: 2

Views: 42036

Answers (3)

PTP
PTP

Reputation: 1

Picking up this old thread to share what has recently worked for me. I was getting an ODBC error, incorrect syntax near keyword 'order' in a union query, which was odd because but I didn't have an order by clause in my SQL.

So I put one in. Still got the error. I bracketed my select statements. No good. I tried union all. Ppfft..nutin. I took the order by statement out. Same error. I opened query properties and tried order by there. Same error. I banged my head against the wall. Still got the error. I hit my workstation repeatedly with a baseball bat. Error surprisingly did not go away.

The union query executed fine in SSMS, so I figured this had to do with the ODBC driver screwing up the SQL translation. I read a lot about SQL injection, which helped a little but not much.

I discovered through a lot of research that the error message was a symptom that my Access based union query SQL was getting translated wrong by the ODBC engine when passed to SQL server, and to use a pass through query instead.

What I then learned was to mark the union query as a pass through and provide the ODBC connection string in the query properties. To find the properties, view your SQL and click "property sheet" in the toolbar. Then put in your ODBC connection string. What this does is bypass the ODBC server and has your query talk directly with SQL Server. Popped the connection string in, ran the query and BAM! Error free.

I don't know if it's relevant, but for those looking for an answer, try turning making your union query a pass through.

Upvotes: 0

SqlRyan
SqlRyan

Reputation: 33914

If you move the ORDER BY to the first SELECT in your UNION, it will order the entire set of results. It has to be on the first SELECT, though, and anywhere else will result in the error you're seeing.

Upvotes: 1

user330315
user330315

Reputation:

In a union you can only order the full result, not the partial ones because ordering a sub-select doesn't make sense - it's a relation/table and they are (by definition) not sorted.

If you want the overal result to be ordered you can add an ORDER BY 1 ASC to sort by the first (and only) column. If you want all rows from the first part to come before the second and sort the second one by weight, you can do the following:

select id
from
(
  SELECT COUNT(*) AS ID, 
         0 as sort_column
  FROM Employees i
    INNER JOIN #WeightedIDs w
    ON (i.ID = w.ID)
  WHERE (i.DepartmentID = 10 and i.ShiftID = 2)

  UNION ALL

  SELECT i.ID, 
         x.[Weight] 
  FROM Employees i
    INNER JOIN #WeightedIDs w
    ON (i.ID = w.ID)
  WHERE (i.DepartmentID = 10 and i.ShiftID = 2)
) x
order by sort_column

This assumes that the values for x.weight are greater than zero. If they aren't you need to use a different (lower) value in the first part of the union.

Upvotes: 6

Related Questions