Subquery sort - 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

Transact-SQL (2000)

Subquery sort

Author

Topic

megatim
Starting Member

2 Posts

Posted - 2005-04-01 : 05:04:21

Hi all. I have this query.

SELECT Col1, Col2
FROM (
SELECT Table1.Col1, Table1.Col2
FROM Table1, Table2
WHERE bla bla
ORDER BY Table1.Col2
)
WHERE Col2 BETWEEN 'A' AND 'D'

It should return the Col2 items between 'A' and 'B', looking into a sorted table. Right?
So, if the unsorted Table1.Col2 is

(Unsorted) Col2
---------------
E
A
C
B
F

, then the subquery should return it sorted i.e.

(Sorted) Col2
-------------
A
B
C
D
E

and the full query should return

Col2
------
A
B
C
D

Well, it doesn't. Could somebody help, please?

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-01 : 05:17:30

You still need an ORDER BY on the outside query.

-------
Moo. :)

megatim
Starting Member

2 Posts

Posted - 2005-04-01 : 05:25:02

Thanks for the reply.
Tried it. No difference ... :(

The weird thing is that, on it own, the internal SELECT returns the result correctly sorted i.e.

A
B
C
D
E
F

What is going on here?

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-01 : 05:37:48

Okay. Well firstly the full query will never return

Col2
------
A
B
C
D

Because you have it returning two columns.

Secondly I'm not sure why you can't just do all of this in one query.

Post some sample data and the complete query.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources