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,
Ali ShahSQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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 DeveloperAuthor Commented:
Yes for sure for example the data in the table is as below
DataExample.jpg
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

ste5anSenior DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ali ShahSQL DeveloperAuthor 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,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.