Order by inside a subquery

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 com开发者_StackOverflowmon table expressions, unless TOP or FOR XML is also specified.

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.

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

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

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