May 7, 2013 at 12:47 am
Hello,
I came across a situation in SQL 2008 to fetch only single row from a table even if each of the multiple OR condition satisfies for multiple records.
Insert into #Table1 (Col1, Col2, Col3) values (1, 2, 'Test')
Insert into #Table1 (Col1, Col2, Col3) values (1, NULL, 'Test')
Insert into #Table1 (Col1, Col2, Col3) values (NULL, NULL, 'Test')
Select*
from#Table1
Where((Col1 in (1, 2) and Col2 in (1, 2))
or((Col1 in (1, 2) and Col2 is null) or (Col2 in (1, 2) and Col1 is null))
or(Col1 is null and Col2 is null))
andCol3 = 'Test'
In the above code, lets says I have three records in table and each record satisfies only one but any condition mentioned in OR cases. If I execute the script above it will give me all three records. What I need is: Compare all records in table
If all conditions in OR case satisfies, then display the record that satisfies the first OR condition only.
If second and third conditions in OR case satisfies, then display the record that satisfies the second OR condition only.
If only the third condition in OR case satisfies, then display that record only.
Is it possible to do that in sql without using multiple selects for each condition? In above case, i need to display only the first record which satisfies (Col1 in (1, 2) and Col2 in (1, 2))
May 7, 2013 at 2:08 am
You can add a CASE statement and use this to assign a specific value to the row, depending on the condition. By sorting on this value in combination with a TOP 1 you will get the desired row.
create table #Table1 (Col1 int, Col2 int, Col3 nvarchar(5))
Insert into #Table1 (Col1, Col2, Col3) values (1, NULL, 'Test')
Insert into #Table1 (Col1, Col2, Col3) values (NULL, NULL, 'Test')
Insert into #Table1 (Col1, Col2, Col3) values (NULL, 2, 'Test')
Select TOP 1
*
, case
when (col1 in(1, 2)) then 1
when ((Col1 in (1, 2) and Col2 is null) or (Col2 in (1, 2) and Col1 is null)) then 2
when (Col1 is null and Col2 is null) then 3
else 4
end as 'Fetch'
from#Table1
Where((Col1 in (1, 2) and Col2 in (1, 2))
or((Col1 in (1, 2) and Col2 is null) or (Col2 in (1, 2) and Col1 is null))
or(Col1 is null and Col2 is null))
andCol3 = 'Test'
order by 4
drop table #Table1
If you don't want the extra column with the CASE value in your result set, move this CASE statement to the ORDER BY clause (instead of the 4 in above sample)
May 7, 2013 at 2:37 am
That solution works well.
Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply