05-16-2012 02:32 PM
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:
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?
05-16-2012 04:11 PM
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.
create table want as
( select loan_id, max( execution_id ) as max_id
group by load_id ) s
m.loan_id = s.loan_id and
m.execution_id = s.max_id
05-17-2012 10:52 AM
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.