Avatar of Ali Shah
Ali Shah
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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

Avatar of undefined
Last Comment
Ali Shah

8/22/2022 - Mon
Vitor Montalvão

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

ASKER
Yes for sure for example the data in the table is as below
DataExample.jpg
Raja Jegan R

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))

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
ste5an

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ali Shah

ASKER
Thank you very much for both of you. However for some reasons Rajs's code didn't work properly whilst your did. Thanks again.

kindest regards,