December 6, 2007 at 9:02 am
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
December 6, 2007 at 9:21 am
Consider the data in your tables as sets (or bags, if there are duplicates).
In your first query you order the set (the sequences will be 3;2;1, and you take the first two only). If you were not ordering then SQL Server will just pick two rows that satisfy the condition. How it will pick them will depend on how the data is loaded/stored.
Same problem in your second query:
The middle part:
select top 2 * from #source
where cuslink=@cuslink
returns any two rows form your table wich satisfy the condition. You can fix it by:
select top 2 *
from
(
select top 2 * from #source
where cuslink=@cuslink
order by sequence
) w
order by sequence desc
and this is what you have done in your last query. So think about a result sets without "order by" in a way that they are sets, and nothing guaranties any order on them. Selecting the first two rows of such an unordered set will give you two "random/uncontrollable" rows.
Regards,
Andras
December 14, 2007 at 10:55 am
When you use "top" and "order by" together, they work together.
Thus, your first query select 3 and 2, because it first orders by sequence, descending, then it picks the top two rows from that.
Basically, "order by" runs first, then "top".
This sequence of operations explains the behavior of all three of your queries.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply