March 30, 2010 at 10:01 am
Hi,
Recently I came to one confusing issue about using TOP.
Here is an example:
Declare @a1 int ,
Declare @a2 int ,
Declare @a3 int ,
Declare @a4 int ,
Declare @a5 int
select
@a1=20,
@a2=40,
@a3=null,
@a4=50,
@a5=null
Declare @t1 table (attribute_id int, depth int)
;With temp AS
(
select @a1 as attribute_id, 1 depth union all
select @a2 as attribute_id, 2 depth union all
select @a3 as attribute_id, 3 depth union all
select @a4 as attribute_id, 4 depth union all
select @a5 as attribute_id, 5 depth
)
select * from temp where
depth <(select TOP 1 depth from temp where attribute_id is null)
go
--**************************************************
My understanding is it will always return two records so
attribute_id depth
20 1
40 2
my confusion abt this subselect statemtn:
(select TOP 1 depth from temp where attribute_id is null)
is it possible it may not return depth=3 always but it may also return depth=5
can anyone clarify this issue?
Thanks in advance
March 30, 2010 at 10:14 am
Yes, absolutely. It could return any depth where the attribute_id is NULL.
TOP simply returns the first N rows that are returned by the query, it has no inherent order.
The order of a select statement can only be guaranteed by including the order by clause.
e.g. select TOP 1 depth from temp where attribute_id is null ORDER BY depth
If your next question is going to be along the lines of how can you order it by the order in which they were inserted, the answer is that you can't without having some other column (such as an identity) that records this order and you include this in an order by clause!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply