SQL - Postgres - return maximum value for latest date [duplicate]

Question

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

I have following table:

Existing table in SQL

My requirement is follows:

I need, for each Frequency (monthly & weekly), pick latest Date & its maximum Version of that latest Date. Then select records for all Dim & both Frequencies where Date and Version are same as the earlier picked.

For example: There will single latest date & its maximum Version for 'Monthly' Frequency & single date & its maximum Version for 'Weekly' Frequency. Now for all Dim (A & B in our case), just return data where Date & Frequency are same as earlier.

So there will be total 4 rows:

Dim 'A' Monthly
Dim 'A' Weekly
Dim 'B' Monthly
Dim 'B' Weekly

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

ScaisEdge · Accepted Answer · 2017-12-29 13:23:54Z

2

you could use a join with a subquery for get max date and then the max version

select s.Dim, s.Frequency, s.Date, max(s.Version)
from sample_tbl s 
inner ( 
SELECT Dim, Frequency, max(Date) as max_date
FROM   sample_tbl 
group by Dim, Frequency
) t on t.Dim = s.Dim, t.Frequency = s.Frequency t.max_date = s.Date 
GROUP BY s.Dim, s.Frequency, s.Date ;

answered Dec 29, 2017 at 13:23

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

3 Comments

tonypdmtr Over a year ago

With some minor typos fixed, it works (INNER JOIN, use AND between ON conditions)

Parikshit Shinge Over a year ago

Thanks for your reply. This query is returning latest Date & max Version for each Dim. I want only Dim where Date is global latest Date for 'monthly' Frequency & where Date is global latest Date for 'weekly' Frequency with their max Version.

ScaisEdge Over a year ago

update your question .. add a clear data sample and the expected result ..please

Collectives™ on Stack Overflow

SQL - Postgres - return maximum value for latest date [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related