CODEWITHSUNDEEP
sqlsql-server
thuetten
thuetten

Reputation: 25

SQL - Select column value if other value is not present

I have a query in SQL designed like below:

Column A    Column B    Column C
Mary               24             First 
Mary               25             Second
Paul                5             First 
Tim             1             Second

I want to return all rows with "First" in column C, and rows with "Second" only if it does not have corresponding "First" value based upon column A. So I'd like to see:

Mary 24
Paul 5
Tim 1

I've tried Case statements and If statements, but can't get it to work. Any suggestions?

Upvotes: 0

Views: 1097

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

Here is one trick using ROW_NUMBER

SELECT * FROM 
(
SELECT * , ROW_NUMBER()OVER(PARTITION BY [Column A] ORDER BY [Column C] ASC) RN
FROM TableName
) A
WHERE RN = 1

Alphabetically F comes before S so the row number 1 will be assigned to First. If First is not present then 1 will be assigned to Second

Upvotes: 8

Aks
Aks

Reputation: 395

A simple answer will be to use NOT IN.

So, this take all people when Column C = First OR C = Second and they have no other occurence with FIRST)

SELECT [Column A], [Column B]
FROM tableName
WHERE [Column C] = 'First'
OR 
([Column C] = 'Second'
AND [Column A] NOT IN (
    SELECT DISTINCT [Column A] 
    FROM tableName 
    WHERE [Column C] = 'First'))

I'm not sure it'll be efficient in terms of speed but it's easy to understand.

I can't test by now, so please comment if I did a mistake.

Upvotes: 0

Related Questions