TOP and ORDER BY causes confusion

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)

TOP and ORDER BY causes confusion

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2007-12-06 : 10:59:55

Buddies,
I have a sample scripts like:


create table #source(
	[cuslink] int,
	[sequence] int
)
insert into #source values (2233, 1)
insert into #source values (2233, 2)
insert into #source values (2233, 3)

declare @cuslink int
set @cuslink=2233

And I expect the output should be (order of 'Sequence' in output is important):


cuslink     sequence    
----------- ----------- 
2233        2
2233        1

I run the 3 following scripts. 2 of them give me wrong output; the the last script gives me right answer. I do not know how they really work. Thanks for your ideas.


-- Wrong output. Why?
select top 2 * from #source
where cuslink=@cuslink
order by sequence desc

-- Wrong output. Why?
select top 2 *
from
(
	select top 2 * from #source
	where cuslink=@cuslink
) w
order by sequence desc


-- Right output. Why?
select top 2 *
from
(
	select top 2 * from #source
	where cuslink=@cuslink
	order by sequence
) w
order by sequence desc

-- drop
drop table #source

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 11:06:04

Using only TOP (without ORDER BY) does not guarantee the order of output.
Thus your derived table w in first case can be ANY two records.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources