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
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;
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 = , @B =  From ( Select id, ROW_NUMBER() over(order by id) From @data ) v(id, n) PIVOT ( max(id) FOR n in (, ) ) as piv
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.
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
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