How to select first available record and skip others

Hi there,

in My table i have a field called primry, This can contain the data of 1, 0 or NULL for one person. I need to see if there are three records against one persons containing a row for each 1, 0 and NULL then then it selects only that row containing the record 1 ignoring the 0's and NULL rows and if the column contains 0 and NULL then it brings back the row with zero else it brings back the row with null.

can i do it without using cursor or inline function as the data returned is in thousands and cursor or inilne function will make it very slow.

kindest regards,

Microsoft SQL Server 2008Microsoft SQL Server

Last Comment

Ali Shah

8/22/2022 - Mon

Vitor Montalvão

6/25/2015

Can you post some sample data?
A sample with the source data and another sample with the expect result for the same dataset.

Ali Shah

6/25/2015

ASKER

Yes for sure for example the data in the table is as below
DataExample.jpg

Raja Jegan R

6/25/2015

This one should work, kindly change the column names..

create table mytable ( a int, primry int)

insert into mytable values (1, 1)
insert into mytable values (1, 0)
insert into mytable values (1, NULL)
insert into mytable values (2, 0)
insert into mytable values (2, NULL)
insert into mytable values (3, NULL)
insert into mytable values (4, NULL)
insert into mytable values (4, 1)
insert into mytable values (4, 0)
insert into mytable values (5, 1)
insert into mytable values (5, 0)

;with cte as (
select *, case when primry = 1 then 1 when primry = 0 then 2 else 3 end order_val
from mytable )
select a, primry 
from cte
WHERE order_val = 1
union all
select a, primry
FROM cte t1
WHERE order_val = 2
and NOT EXISTS (SELECT * FROM cte t2 WHERE t1.a = t2.a and t2.order_val = 1)
union all
select a, primry
FROM cte t1
WHERE order_val = 3
and NOT EXISTS (SELECT * FROM cte t2 WHERE t1.a = t2.a and t2.order_val IN (1,2))