Most Recent Records

[MauiBound]

I am trying to run a query in Enterprise Guide that gives me the most recent records.  I am working with mortgage tables that show multiple submits per LOAN_ID.  So what I am trying to do is to return the latest full record (50 fields) with the LOAN_ID being unique in the output.  To find the latest record, I can use either of 2 fields:

ANALYSIS_DATE (DATE)

EXECUTION_ID (NUMERIC - AUTONUMBER)

I realize that I can group by LOAN_ID and take the MAX of either of these fields, but that does not give me then newest entire record.  Can anybody point me in the right direction?

Mahalo.

[FloydNevseta]

I don't use EG much, but in SQL query you have to join the summary query to the original table like in the query below. Maybe that will give you a clue to what you have to do in EG.

proc sql;

create table want as

select

     m.*

from

     mortgage_table   m

     join

     ( select loan_id, max( execution_id ) as max_id

          from mortgage_table

          group by load_id )   s

     on

     m.loan_id = s.loan_id and

     m.execution_id = s.max_id

;

quit;

[Linlin]

you can get what you want by using  Sort task , not the query builder. first sort you data by loan_id and date, then sort by loan_id again and select the sort option

'only keep the first record of each 'Sort By' '. please check the attached screen shot.

[MauiBound]

Thank you Linlin.  That was exactly what I was looking for!:smileycool:

[Petersas]

you can sort in the desc order and keep only the 1st record as mentioned above!!!