How to select first available record and skip others
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,
Who is Participating?
Experts Exchange Solution brought to you by
"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.
DataExample.jpg
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))
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;
Experts Exchange Solution brought to you by
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
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
From novice to tech pro — start learning today.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
A sample with the source data and another sample with the expect result for the same dataset.