CODEWITHSUNDEEP
sqlnetezza
MarcelNL
MarcelNL

Reputation: 13

Given Netezza does not support First and Last when aggregating, how to proceed?

I would like to group data on some column called CustID and select their first or the last mortgage even if the mortgages were originated on the same date. How do you do that in Netezza? In MS Access I normally use the First or Last aggregation functions for that.

Data comes like this:

CustID  mortgageID  pass_dt

101      090234W    1-23-1989

101      103120X    5-20-2020

101      103121V    5-20-2020

So here I want either the second or the third record but not both when as extra criterium pass_dt = 5-20-2020.

Thanks very much!

Upvotes: 1

Views: 53

Answers (2)

Harish Mara
Harish Mara

Reputation: 1

select CustID, max(mortgageID), max(pass_dt) from t1 group by 1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269673

If you want the entire record, use window functions:

select t.*
from (select t.*, row_number() over (partition by custid order by pass_dt desc) as seqnum
      from t
     ) t
where seqnum = 1

Upvotes: 1

Related Questions