Click here to Skip to main content
15,920,508 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
how can i run out from this error with out to make another table for result of
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



this is my query
SQL
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))



and this is the error

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.
Posted
Comments
PhilLenoir 19-Sep-14 12:13pm    
Why do you need an ORDER BY in the inner subquery? The "IN" doesn't care which order the results are presented in.

1 solution

SQL
Hi,


Please check if below solution works for you.
Technically you can use EXISTS clause instead of IN if have to use ORDER BY Clause to sort the Sub queries.


UPDATE dbo.Sheet1$ SET F01 = 0 WHERE ID IN
(
    SELECT TOP 3 ID FROM dbo.Sheet1$ WHERE EXISTS
    (
        SELECT TOP 100 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
    )
    ORDER BY ID DESC
)



I agree with comments of PhilLenoir. It is not required to use order by clause in the sub query when you are checking the result with IN.
 
Share this answer
 
Comments
FarshadSh 19-Sep-14 15:42pm    
when i first run below code:
SELECT TOP 100 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

the rows 1602,1607 and 1611 are the top of the table
and when i run your code the sql say: (3 row(s) affected)

but when i check, i see the row 1602,1607 and 1611 don't have any change.

what is the problem?
FarshadSh 19-Sep-14 15:55pm    
when i run this code:

SELECT TOP 3 ID FROM dbo.Sheet1$ WHERE EXISTS
(
SELECT TOP 100 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
)
ORDER BY ID DESC

sql give me just the three final rows of the dataset, and this rows even the value of F01 column aren't 1.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900