How to get max dates from different columns in same table

Question

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

One row per TrackNo or one row per Accountno or one row per AccountNo, TrackNo? Overly simplistic sample data will not reveal logic errors in your group definition. Notice how the two responses make different assumptions. — SMor
– SMor, Commented Aug 24, 2021 at 12:06
The answers got me on the correct track and I came up with a solution. Did use max and a subquery. Thank you for helping — collie
– collie, Commented Aug 24, 2021 at 17:10

Gordon Linoff · Accepted Answer · 2021-08-24 11:42:29Z

1

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;

answered Aug 24, 2021 at 11:42

Gordon Linoff

1.3m62 gold badges704 silver badges856 bronze badges

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

Comments

Chris Kuliukas · Accepted Answer · 2021-08-24 12:01:18Z

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

Collectives™ on Stack Overflow

How to get max dates from different columns in same table

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related