Sort by date most recent, with a twist?

[todder]

My query returns many records for each loan loan number. I want to display only one record for each loan number. Each record has a different date. I would like to display the record with the most recent date for each loan number and sort the report in accending order on the date.

This forum offered advice on how to display the record with the max date but the solutions required a sort by loan number prior to displaying the max date value for that loan number. This seems to preclude sorting all loan number records by date after the max date value is found.

 

[rhinok]

1) Group by loan number
2) Create a Maximum Summary of the date - move this summary field into the Group Header with the Loan number and suppress or hide your detail data.
3) Navigate to the Menu Report|TopN/Sort Group Expert
4) You should see a tab for your Loan Number Group - Select 'Sort All' of your Maximum Summary Field in Descending order (you'll see what I mean when you get to this point)
5) Click 'OK' - you're done!

 

[balves]

Perhaps someone has a trick to do it with Crystal. I don't see one. You must group by loan number to do a Max(datefield). Then what?

The solution is easy as a stored procedure. You create a temp table with the first set of results, then just re-sort it.

CREATE PROC myProcForReport
AS
CREATE TABLE #T1 (loanid integer, latest_date datetime)

INSERT #T1
SELECT loanid, max(datefield)
FROM mytableofloans
GROUP BY loanid

SELECT * #T1 ORDER BY latest_date