How to fix incorrect syntax near keyword 'ORDER'

Question

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.

When you do an `UNION` as shown in your query, the `ORDER BY` clause applies to both parts of your query, thereby resulting in an error. — Web User, Aug 02 '12 at 17:31
Curious about what you're trying to accomplish - returning the COUNT of rows in a query as an ID, followed by the actual rows? — n8wrl, Aug 02 '12 at 17:31
@n8wrl: the first row will return the total number of rows in the union result. — , Aug 02 '12 at 17:35

score 6 · Accepted Answer · answered Aug 02 '12 at 17:34

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.

Thanks! This is what I've been looking for. Now I want to add pagination to the second query. Please take a look at: http://stackoverflow.com/questions/11783023/how-to-add-pagination-to-the-following-sql — Mohamed Nuur, Aug 02 '12 at 17:51
Why do you say it doesn't make sense? I give you an example: I need the first row ordered by date and the last row ordered by date. To do this I have two sub-selects two get the top 1 ordered by date asc and top 1 ordered by date desc. But it won't let me use union. — Daniel, Sep 25 '15 at 14:14

score 1 · Answer 2 · answered Aug 02 '12 at 17:47

1

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.

answered Aug 02 '12 at 17:47

SqlRyan

33,116
33
114
199

How to fix incorrect syntax near keyword 'ORDER'

2 Answers2