Order by inside a subquery

Question

SELECT u.id
FROM user u
WHERE u.id IN 
((SELECT l.id FROM location l WHERE l.id = ?id ORDER BY l.idLocation DESC )) 

What I want to do is make this Order By works. But It's not possible to have a order by inside a view. How can I do this order by if not inside a view?

SQL Response:

Msg 1033, Level 15, State 1, Line 5 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.

Solution 1

You can't. Only the outermost ORDER BY matters.

So SELECT * FROM MyView ORDER By Whatever is the only way.

Any intermediate ordering (whether ORDER BY or coincidental, or part of the lan) is ignored

Solution 2

There's two issues here.

1 - Your ORDER BY is pointless. When you use an IN subquery, ORDER is irrelevant.

2 - The ORDER BY for a view should be in the calling query, not in the view itself.

Solution 3

I share the opinion with the previous answers that it is not generally a good idea to do an order by in a sub-query. However, I have found that it is sometimes very useful to do just that. I assume the sample query in pringlesinn's question is a simple example of a more complex requirement. You can do an order by in a sub-query this way:

SELECT u.id
FROM user u
WHERE exists
(SELECT ROW_NUMBER() OVER (ORDER BY l.idLocation DESC) FROM location l WHERE u.id = l.id and l.id = ?id) 

You'll notice however that the WHERE clause no longer uses an 'IN' operator. Instead you have to use 'EXISTS' and do a correlated sub-query. Or you could do something like this:

SELECT u.id
FROM user u inner join 
  (
    SELECT ROW_NUMBER() OVER (ORDER BY l.idLocation DESC) as rowid, l.id 
    FROM location l 
    WHERE l.id = ?id
  ) as z on z.id = u.id

Best Wishes, James

Solution 4

There is no need to use ORDER BY in the subquery.

If you want to add an ORDER BY to the view you can add a TOP(100) PERCENT to your view and then you can use it.

http://cf-bill.blogspot.com/2007/01/sql-server-order-view.html

View more solutions

Comments

[Gondim]

Thanks. Poor of me, i'm gonna have to find another way to do it then.

[Damien_The_Unbeliever]

@pringlesinn - do what? - that's the great mystery at the moment.

[James L.]

As gbn mentioned, the order by is ignored in the inner query. I checked execution plan of these two code snippets and the order by did appear to be eliminated. I do recall however that using the above approach in a much more complex situation was the best way to achieve the task. Good luck!

[Cheeku]

If I have an table of articles and I need to display them ordered A-Z or Z-A but I also want to select 100 at a time. ORDER BY outside orders only the selected 100. How to do that for all?

[Charles Burns]

This is not correct. Many situations benefit from an inner ORDER BY. Pagination queries are a common example. Ordering within the subquery gets rows ordered at a table-scope. The outermost ORDER BY orders only within the scope of the final results.