Link to home
Start Free TrialLog in
Avatar of Ali Shah
Ali ShahFlag 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,
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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

ASKER

Yes for sure for example the data in the table is as below
DataExample.jpg
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

ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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,