How to use ORDER BY in subqueries

Question

update dbo.Sheet1$ set F01 = 0 where ID in(
select top 3 ID from dbo.Sheet1$ where ID in(
select ID, ISNULL(F01,0) + ISNULL(F02,0) + ISNULL(F03,0) as RowSum 
   from dbo.Sheet1$ where F01 = 1 AND F02 = 1 order by RowSum desc))

Running this code I get the error message:

Msg 1033, Level 15, State 1, Line 1 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

can I write another query that exactly does the same like above query?

using top in subquery select may solve your peroblem `SELECT TOP 2147483647` — Farshad, Sep 20 '14 at 06:39
possible duplicate of [SQL Error with Order By in Subquery](http://stackoverflow.com/questions/985921/sql-error-with-order-by-in-subquery) — bummi, Sep 20 '14 at 06:49

score 1 · Accepted Answer · answered Sep 20 '14 at 06:43

Your query has more issues than just ordering without sampling. For example, when you use a subquery inside the IN () predicate, it cannot return more than 1 column, while your returns two.

Check this one, maybe I have guessed it correctly:

update dbo.Sheet1$ set F01 = 0
where ID in (
    select top 3 ID
    from dbo.Sheet1$
    where F01 = 1 AND F02 = 1
    order by ISNULL(F01,0) + ISNULL(F02,0) + ISNULL(F03,0) desc
);

score 0 · Answer 2 · answered Sep 20 '14 at 07:13

2 possible reasons for the errors are:

1) You subquery has condition IN which tries to look for 2 column in the subquery while you can have only 1 column while you query IN

2) Your subquery can not use Order By because whether you're going to sort or not, it'll look for matching records from the subquery result set and update the records. So there won't be any meaning of using Order By in your subquery. Whether you'll keep Order By or remove Order By in your subquery, it'll give you the same output.

Hope this helps!

How to use ORDER BY in subqueries

2 Answers2