CODEWITHSUNDEEP
sql-servert-sqlselect
Siddu
Siddu

Reputation: 333

SQL Server select (top) two rows into two temp variables

I have a query which results in two or more rows (just one column) and I want to catch the first row value into first temp variable and second row value into second temp variable without using multiple times the select top 1 and select top 1 order by desc

Something like this;

Select row1 value into @tempvariable1, row2 value into @tempvariable2 from blah blah

Upvotes: 2

Views: 4509

Answers (3)

jean
jean

Reputation: 4350

You have two options

Let's say we test case is build as below

create table dbo.Test
(
   value varchar(100) not null
)

GO

insert into dbo.Test
values
('A'),('B'),('NO THIS ONE'),('NO THIS ONE'),('NO THIS ONE')

GO

Now let's say you fetch your data as below

select t.value
 from dbo.Test t
 where t.value != 'NO THIS ONE'
 GO

The first and easier option is to save the data in a temp table

  declare @results as Table (value varchar(100))

 insert into @results
 select t.value
 from dbo.Test t
 where t.value != 'NO THIS ONE'

you still use TOP 1 BUT not in the entire data, only in the results.

Use TOP 1 to find the first result and a second TOP 1 where value is different from the first.

declare @A varchar(100), @B varchar(100)

set @A = (select top 1 r.value from @results r)
set @B = (select top 1 r.value from @results r where r.value != @A)

select @A, @B
GO

This approach have the advantage of performance. Of course that don't work great if both values are equal. You can fix it by using a top 1 and ordering in the inverse order.

There's a better alternative using rownumber. It works because if you set a variable when returning multiple rows the varible sticks with the last one (in fact it's reseted for each row iteration).

The case statement makes sure the variable @A is seted only on the first row iteration.

declare @A varchar(100), @B varchar(100)

/* This way @B receives the last value and @A the first */
select @B = t.value,
 @A = (case when ROW_NUMBER() OVER(order by t.Value) = 1
 then t.Value else @A
 end)
 from dbo.Test t
 where t.value != 'NO THIS ONE'

 select @A, @B

Upvotes: 0

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

You can use a CTE where you will get the X values you need and then select from it:

declare @data table(id int);
insert into @data(id) values(8), (6), (4), (3);

with vals(id, n) as (
    Select top(2) id, ROW_NUMBER() over(order by id)
    From @data      
) 
Select @A = (Select id From vals Where n = 1)
    , @B = (Select id From vals Where n = 2)

You could also use PIVOT:

Select @A = [1], @B = [2]
From (
    Select id, ROW_NUMBER() over(order by id)
    From @data
) v(id, n)
PIVOT (
    max(id) FOR n in ([1], [2])
) as piv

Upvotes: 1

gotqn
gotqn

Reputation: 43636

You need somehow to identify the row (I am using a row ID in the example below, ordering by value - you can order by id or something else): 

DECLARE @DataSource TABLE
(
    [value] VARCHAR(12)
);

INSERT INTO @DataSource
VALUES ('value 1')
      ,('value 2')
      ,('value 3');

DECLARE @tempVariable1 VARCHAR(12)
       ,@tempVariable2 VARCHAR(12);

WITH DataSource ([value], [rowID]) AS
(
    SELECT [value]
          ,ROW_NUMBER() OVER (ORDER BY [value])
    FROM @DataSource
)
SELECT @tempVariable1 = IIF([rowID] = 1, [value], @tempVariable1)
      ,@tempVariable2 = IIF([rowID] = 2, [value], @tempVariable2)
FROM DataSource;

SELECT @tempVariable1
      ,@tempVariable2;

Upvotes: 4

Related Questions