Avatar of karen1974
karen1974
Flag for United States of America asked on

SQL query to compare two values and choose the "more favorable"

Hello all,

I'm trying to write a piece of SQL that looks at 2 outcomes -- either "Yes" or "No" -- and assigns the more favorable of the 2 ("Yes") when a memberID comes up multiple times in a search.

I think it should be straightforward and I checked solutions but here is what I have and cannot get right.

Any help is much appreciated!

SELECT DISTINCT
--CASE WHEN COUNT(ID) <> 1 THEN Outcome_Met = 'YES' END as CorrectNum,
tp.*
INTO #temp_num
FROM #temp_memID tp --where have pulled needed contact info about member
HAVING count(RID) > 1
GROUP BY RID, Numerator_Met
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
karen1974

8/22/2022 - Mon
Francis Omoruto

It really would be best to provide some illustrative data that you can arrange in a worksheet with the expected result.
Cheers!
Aaron Tomosky

Start by Doing just a select query that gets the results you want. Don't do the insert yet as your insert is all jumbled in there. We will add that at the end.

So first select what you want to output, group by, and count. Take a crack at that and post that and the data it pulls herr
Anthony Perkins

Something like this perhaps:
SELECT  RID,
        Numerator_Met,
        MAX(Outcome_Met) AS CorrectNum
INTO    #temp_num
FROM    #temp_memID tp --where have pulled needed contact info about member
GROUP BY RID,
        Numerator_Met
HAVING  COUNT(RID) > 1

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Aaron Tomosky

Yep. Sorry I'm on my phone and so not typing out full SQL.

I suggest going with
Insert into whatever
Select whatever from whatever group by something.

By usin this style, you can do the select, then just add the insert above it. Much easier to build an troubleshoot IMO. Select into blah from blah is hard to read, hard to write and test.
ASKER CERTIFIED SOLUTION
G Trurab Khan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
karen1974

ASKER
The query for the below works in giving me the "offending" duplicates where I wanted to get only the "YES" outcome instead of the "NO" --  thank you very much!

And great advice from others on working with just SELECT and not SELECT INTO when facing a challenge.

I would like to be able to now remove these duplicates from my "master" list so that I don't have to go through de-duping them in a spreadsheet later -- I imagine I could do this by a subquery?

SELECT RID,
'YES' as Numerator_Met
FROM #temp_memID
group by rid
HAVING COUNT(RID) > 1