How Can I Use Order By In Subqueries Without Make Another Table For Result Of The Query That Order By Is Used In That Query

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

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 19-Sep-14 5:28am

FarshadSh

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Baliram Suryawanshi · Answer 1 · 2014-09-19T08:29:00

Solution 1

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.

Posted 19-Sep-14 8:29am

Baliram Suryawanshi

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.