How to select first available record and skip others

Ali Shah
Ali Shah used Ask the Experts™
on
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,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Can you post some sample data?
A sample with the source data and another sample with the expect result for the same dataset.
Ali ShahSQL Developer

Author

Commented:
Yes for sure for example the data in the table is as below
DataExample.jpg
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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

Senior Developer
Commented:
Using Raja's data:

DECLARE @mytable TABLE ( a INT, primry INT );

INSERT  INTO @mytable
VALUES  ( 1, 1 ),
        ( 1, 0 ),
        ( 1, NULL ),
        ( 2, 0 ),
        ( 2, NULL ),
        ( 3, NULL );


WITH    Ordered
          AS ( SELECT   M.a ,
                        M.primry ,
                        ROW_NUMBER() OVER ( PARTITION BY M.a ORDER BY CASE WHEN M.primry IS NULL THEN -1
                                                                           ELSE M.primry
                                                                      END DESC ) AS RN
               FROM     @mytable M
             )
    SELECT  O.a ,
            O.primry ,
            O.RN
    FROM    Ordered O
    WHERE   O.RN = 1;

Open in new window

Ali ShahSQL Developer

Author

Commented:
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,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial