Order by in subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Order by in subquery

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-22 : 04:18:03

hi

i am bit confuse when can and when cannot "using order by subquery"

when we give order by clause in subquery sql server gives a error order by clause in subquery not allowed...
today i gone throug a article which gives this example

SELECT TOP 10 account_id FROM bill_process
WHERE
account_id (SELECT TOP 1 account_id FROM bill_process ORDER BY Qty_usg desc )
ORDER BY Qty_usg

and it work well so bit confuse

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-22 : 04:20:54

just now tried using top we can ...
if i am going to give a very high top * it still works does it effect the performance

======================================
Ask to your self before u ask someone

Nazim
A custom title

1408 Posts

Posted - 2002-04-22 : 04:28:11

khalik AFAIK, you can use order by in a sub query if accompanied with a top statment (coz tht helps in filtering of rows). otherwise it doesnt makes sense using a order by in your sub query as the results are only interemdiary , they are basically used as input to the outer query. so, it doesnt makes sense in which order they come.

--------------------------------------------------------------

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-22 : 04:34:43

hi nazim thanks for info... yea i got it but what about performance

by the way what is AFAIK,

======================================
Ask to your self before u ask someone

Nazim
A custom title

1408 Posts

Posted - 2002-04-22 : 04:42:59

remember an Order by always tells on the performance.

about AFAIK - As far as i know
IMHO - In my Humble Opinion.

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources