SQL query using WHERE IN clause

Question

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
);

Please read meta.stackoverflow.com/questions/285551/… and the accepted answer — user330315
– user330315, Commented Dec 29, 2017 at 11:17
Possible duplicate of SQL - Postgres - return maximum value for latest date — James Douglas
– James Douglas, Commented Jan 2, 2018 at 20:32

Gordon Linoff · Accepted Answer · 2017-12-29 11:49:35Z

1

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.

answered Dec 29, 2017 at 11:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Parikshit Shinge Over a year ago

Thanks Gordon. Is there way where I can get the latest Date for 'monthly' & latest Date for 'weekly' frequencies and pick all rows for Dim where Date & Frequency are whatever we got in earlier?

Gordon Linoff Over a year ago

@ParikshitShinge . . . Ask another question with appropriate sample data and desired results.

Collectives™ on Stack Overflow

SQL query using WHERE IN clause

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related