CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
analytica
analytica

Reputation: 105

How to filter to get one unique record using SQL

I have a table similar to this. If there is a confirmed record, I want to select the oldest record and if not, select the most recent one. In this case, I would want the 4_A record.

ID Record Type Date
1_A 1 auto 4/7/2021
2_A 1 confirmed 4/1/2021
3_A 1 suggested 4/5/2021
4_A 1 confirmed 4/2/2021
5_A 1 suggested 4/5/2021

I've been able to use the a window function and QUALIFY to filter the most recent one but not sure how to include the TYPE field into the mix.

SELECT * from TABLE  WHERE QUALIFY ROW_NUMBER() OVER (PARTITION BY RECORD ORDER BY RECORD,DATE DESC) = 1 ;

Upvotes: 1

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Let me assume that you mean the oldest confirmed date if there is a confrimed:

SELECT * 
FROM TABLE
WHERE QUALIFY ROW_NUMBER() OVER (PARTITION BY RECORD 
                                 ORDER BY (CASE WHEN Type = 'Confirmed' THEN 1 ELSE 2 END),
                                          (CASE WHEN Type = 'Confirmed' THEN DATE END) ASC,
                                          DATE ASC
                                 ) = 1;

If you really mean the oldest date if there is a confirmed, then:

SELECT * 
FROM TABLE
QUALIFY (CASE WHEN COUNT_IF( Type = 'Confirmed') OVER (PARTITION BY RECORD) 
              THEN ROW_NUMBER() OVER (PARTITION BY RECORD ORDER BY DATE)
              THEN ROW_NUMBER() OVER (PARTITION BY RECORD ORDER BY DATE DESC)
         END) = 1;

Upvotes: 1

Related Questions