CODEWITHSUNDEEP
sqlsql-servert-sql
collie
collie

Reputation: 13

How to get max dates from different columns in same table

Need some help please. I have the following table:

Accountno TrackNo PercentInc CreateDate Lastdate
123456 396 3 01/03/2019 24/05/2021
123456 516 0.9 20/01/2020 25/06/2020
123456 516 3 01/07/2013 29/10/2021
123456 396 1 12/12/2018 12/05/2018
123456 396 2 05/09/2019 08/11/2019
123456 516 0.4 08/09/2018 30/12/2020

I need to show results one row per TrackNo - I have to display the PercentInc where the CreateDate is the latest but show the LastDate for that row with the max (LastDate) Results:

Accountno TrackNo PercentInc CreateDate Lastdate
123456 396 2 05/09/2019 24/05/2021
123456 516 0.9 20/01/2020 29/10/2021

Thanks

Upvotes: 0

Views: 746

Answers (2)

Chris Kuliukas
Chris Kuliukas

Reputation: 121

There are many ways to skin a cat with SQL. One approach that I find intuitive:

Get the maximum dates for each TrackNo from a query like this:

SELECT TrackNo, MAX(CreateDate) MaxCreateDate, MAX(Lastdate) MaxLastDate 
FROM Table 
GROUP BY TrackNo

Then link this to the two records you want with a sub-query and two inner joins:

SELECT maxLastDate.Accountno, maxCreateDate.TrackNo, maxCreateDate.PercentInc, maxCreateDate.CreateDate, maxLastDate.Lastdate
FROM (
    SELECT TrackNo, MAX(CreateDate) MaxCreateDate, MAX(Lastdate) MaxLastDate 
    FROM Table 
    GROUP BY TrackNo
) AS maxDates
INNER JOIN Table AS maxCreateDate 
    ON maxCreateDate.TrackNo = maxDates.TrackNo AND maxCreateDate.CreateDate = maxDates.MaxCreateDate
INNER JOIN Table AS maxLastDate 
    ON maxLastDate.TrackNo = maxDates.TrackNo AND maxLastDate.Lastdate = maxDates.MaxLastDate

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use window functions:

select Accountno, TrackNo, PercentInc, CreateDate, max_Lastdate
from (select t.*,
             row_number() over (partition by accountno, trackno order by createdate desc) as seqnum,
             max(lastdate) over (partition by accountno, trackno) as max_lastdate
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions