CODEWITHSUNDEEP
sqlpostgresql
Parikshit Shinge
Parikshit Shinge

Reputation: 35

SQL query using WHERE IN clause

This might be simple but I'm new to SQL and couldn't find how to do this exactly.

and I have following table:

Existing table in SQL

My requirement is follows:

I need, for every Dim, for each Frequency, I need latest Date & maximum Version of that latest Date. For example: There will be one row for Dim 'A' & Frequency 'Monthly' with their latest Date & the latest date's maximum Version. and There will be another row for Dim 'A' & Frequency 'Weekly' with their latest Date & the latest date's maximum Version.

Can anybody please help me with this?

I tried using following query but it not returning correct values:

SELECT Dim, Frequency, Date, Version
               FROM   sample_tbl 
               WHERE  ( Frequency, Date, 
                        Version ) IN ( 
select Frequency, max(Date), max(Version)
from sample_tbl
group by 1
);

Upvotes: 0

Views: 229

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I Postgres, I think distinct on does what you want:

select distinct on (dim, frequency) s.*
from sample_tbl s
order by dim, frequency, date desc, version desc;

For each dim/frequency combination this returns one row. That row is the first row encountered based on the order by clause.

Upvotes: 1

Related Questions