Select Top in Different Variables

Question

Perhaps this is not possible, but I thought I would ask:

I want to get Topand save each in a different variable. I know that it would be possible with 3 elections and taking the 1st, 2nd, 3rd of Top 3, but I was hoping that this is possible in one statement?

those.

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

Answer 1

Suppose you want for demonstration purposes 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_NUMBERfor line numbers, then combines them into a single line result, which is used when assigning to variables.

Answer 2

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