I want to only return the most recent/max value from a set in SQL

Question

I have a list of clients with a Payment ID that has changed over the years, but I'm only interested in returning the info from the most recent. This is my result after running:

     Select distinct 
    a.client 
    , a.payment_id 
    , max(a.update_year_month)  
    from Client_Data a

    group by 1,2

Client	Payment ID	Update Year_Month
A	B	202005
A	C	201907
A	D	201706

I only want my query to pull in the most recent one regardless of the payment_id. However, I need to still bring in Payment ID for my larger query.

To be clear, there are thousands of clients in this table and I need to most recent payment ID for each of them without bringing in the older payment IDs — Mark Cavanaugh, Feb 12 '21 at 18:40

Doug Currie · Answer 1 · 2021-02-12T20:24:48.683

0

Perhaps

select 
    a.client 
  , a.payment_id 
  , max(a.update_year_month)
from Client_Data a
group by 1

or

select 
    a.client 
  , a.payment_id 
  , a.update_year_month
from Client_Data a
where a.update_year_month 
      = (select max(b.update_year_month)
          from Client_Data b where a.client = b.client)

edited Feb 12 '21 at 20:24

answered Feb 12 '21 at 18:26

Doug Currie

40,708
1
95
119

Sorry I should have specified. There are thousands of clients in this table. And I want to get the most recent payment ID for each one and not bring in any of the older Payment IDs. – Mark Cavanaugh Feb 12 '21 at 18:34
Oh, does the updated version do what you want? – Doug Currie Feb 12 '21 at 18:44
I have to group payment_id or it has to be in an aggregate function.. I don't know if that is a netezza specific issue or not. – Mark Cavanaugh Feb 12 '21 at 18:54
Yes must be netezza; my query works in SQLite. – Doug Currie Feb 12 '21 at 20:17
I added a second query that uses a correlated subquery that also works in SQLite – Doug Currie Feb 12 '21 at 20:25
I ended up just making a table without the payment_id and the max update_year_month then joining in the same table. Thanks for your help! – Mark Cavanaugh Feb 12 '21 at 21:51

I want to only return the most recent/max value from a set in SQL

1 Answers1