Keeping only the Record that has the Max Create Date
I want to left join a new table to a current table I have. My new table I am pulling from an existing Qvd, has multiple records for an account number. What is the easiest way, in my report, to only pull the record that is the max CreatedDate, for each unique account number? I need the status (3rd column) from that most recent record.
Re: Keeping only the Record that has the Max Create Date
From QV help
firstsortedvalue ([ distinct ] expression [, sort-weight [, n ]])
Returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a number of records as defined by a group by clause. Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return NULL. By stating an n larger than 1, the nth value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.
FROM [..\..\..\Qvd Applications\QVD_EXTR.qvd](qvd)
Group by EXTR_ACCT_ID;
FirstSortedValue(EXTR_ID,Timestamp#(EXTR_CREATEDDATE,'YYYY-MM-DD hh:mm:ss.fff')) -- Within each group of EXTR_ACCT_ID, EXTR_CREATEDDATE is sorted in ascending order and selects EXTR_ID for the minimum EXTR_CREATEDDATE
FirstSortedValue(EXTR_ID,-Timestamp#(EXTR_CREATEDDATE,'YYYY-MM-DD hh:mm:ss.fff')) -- If minus is used in sort expression then Within each group of EXTR_ACCT_ID, EXTR_CREATEDDATE is sorted in descending order and selects EXTR_ID for the maximum EXTR_CREATEDDATE