Intro
Variables in
select statements take their values from the last row in the record set. To see this, run the code below with and without the
top clause:
- with
top 1, @n will be 2, which is what you would expect
- without
top 1, @n will be 1, because that is the last value when you order by n desc
Code
declare @n int
;with T as (
select n = 1 union
select n = 2
)
select top 1
@n = n
from T
order by n desc
print cast(@n as varchar)