Ordering of the data in nested query

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

General SQL Server Forums

New to SQL Server Programming

Ordering of the data in nested query

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-04-15 : 13:14:16

I have a query like
SELECT DISTINCT PLOT_NAME, METHOD_NAME FROM (Select Plot_Name, Method_Name,sample_start_date from .... where ... order by sample_start_date, plot_name, method_name)

Here, in the nested query I am using order by, but in the outer query there is no order by clause. But it is still ordering it. Is there a way I can keep the original ordering returned by the nested query?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 13:23:07

yup.give the same ordering in main select using order by . there's no use giving order by inside derived table


SELECT DISTINCT PLOT_NAME, METHOD_NAME FROM 
(Select Plot_Name, Method_Name,sample_start_date, plot_name, method_name from .... where ... ) t
order by sample_start_date, plot_name, method_name

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-04-15 : 13:29:29

The thing is: in the nested query I have an additional column (sample_start_date) that I am using to order by. In the outer query, I can not keep this column and that is why I am using a nested query otherwise I could just use a straight-forward query.
And I want to(need to) keep the order returned by the nested query.

Is there a way?
Thanks visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 13:38:28

quote:
Originally posted by sqlbug

The thing is: in the nested query I have an additional column (sample_start_date) that I am using to order by. In the outer query, I can not keep this column and that is why I am using a nested query otherwise I could just use a straight-forward query.
And I want to(need to) keep the order returned by the nested query.

Is there a way?
Thanks visakh.

so what? you can simply add sample_start_date in order by alone without adding it in select list unless you use distinct. does your table have duplicates?

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-04-15 : 13:43:31

Yes, I have duplicates - so I need to use DISTINCT.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 13:51:56

quote:
Originally posted by sqlbug

Yes, I have duplicates - so I need to use DISTINCT.
Thanks.

can you show data sample of how you want data to be sorted?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 13:52:33

SELECT PLOT_NAME, METHOD_NAME FROM .... where ...
group by plot_name, method_name
order by min(sample_start_date, plot_name, method_name

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 13:54:57

quote:
Originally posted by Peso

SELECT PLOT_NAME, METHOD_NAME FROM .... where ...
group by plot_name, method_name
order by min(sample_start_date), plot_name, method_name

E 12°55'05.63"
N 56°04'39.26"

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-04-15 : 14:10:54

Hello Visakh and Peso,
Are you suggesting to use one query only or this is the nested one you are showing?
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 14:12:59

its a single query

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-04-16 : 08:49:32

Your requirement does not make sense.
How can you order by something you have 'distincted away'? Each distinct row can be made from more than one row with differing sample_start_dates

Plot_Name, Method_Name,sample_start_date
P1, M1, 1/1/2008
P2, M2, 1/1/2007
P2, M2, 1/1/2009

What's the value used to order the distinct P2/M2 ? 2008 or 2009? And why? The distinct values are, by definition, the same. Order on anything else makes no sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources