CODEWITHSUNDEEP
sql-serverselect
windowsgm
windowsgm

Reputation: 1616

Select Top into Different Variables

Probably not possible but I thought I would ask;

I want to get the Top and save each into a different variable. I know that it would be possible with 3 selects and taking the 1st, 2nd, 3rd from the Top 3 but I was hoping it might be possible in one statement?

I.E.

Declare @Var1 as int,
Declare @Var2 as int,
Declare @Var3 as int
select Top 3 [SAVE 3 RETURNED RECORDS INTO VARIABLES] from Table

Upvotes: 5

Views: 7224

Answers (2)

Kaf
Kaf

Reputation: 33829

This is another way:

Declare @Var1 as int, @Var2 as int, @Var3 as int
Declare @rn int = 1

select top(3) @Var1 = case when @rn = 1 then val else @var1 end,
              @Var2 = case when @rn = 2 then val else @var2 end,
              @Var3 = case when @rn = 3 then val else @var3 end,
              @rn += 1 
from t
order by val

select @var1, @var2, @var3

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453327

Supposing for demo purposes you want the TOP 3 schema_id FROM sys.objects ORDER BY object_id.

Declare @Var1 as int;
Declare @Var2 as int;
Declare @Var3 as int;


WITH T AS
(
SELECT *,
       ROW_NUMBER() OVER (ORDER BY object_id) RN
FROM sys.objects
)
SELECT @Var1 = MAX(CASE WHEN RN = 1 THEN schema_id END),
       @Var2 = MAX(CASE WHEN RN = 2 THEN schema_id END),
       @Var3 = MAX(CASE WHEN RN = 3 THEN schema_id END)
FROM T 
WHERE RN <= 3;

SELECT @Var1, @Var2, @Var3

It uses ROW_NUMBER to number the rows then pivots them into a single row result that is used in assigning to the variables.

Upvotes: 6

Related Questions