Find most recent record then sort alphbetical 2

[MJRBIM]

OK, maybe it's just because it mid-week...but I can't figure out an easy way to do this one, even though I'm sure I have done it before.

I have a table of codes and descriptions that are updated on occassion. I want to find the most recently updated record from the table, then sort the results alphabetical.

I thought maybe a MAX select statement or a sub-report, but don't seem to be getting what I want...sample is below

ID DESCRIPTION UPDATED_LAST_DATE
============================================================
0001 ACTIVITY REPORTS 05/15/2001
0001 Environmental Compliance Report 05/16/2003
0002 ACTIVITY REPORTS 05/15/2001
0002 CA ENVIRONMENTAL ASSESSMENT REPORT 05/16/2001
0002 CA: Environmental Assessment Report 01/30/2004
0028 ANNUAL AUDITED FIN. STMTS. 05/15/2001
0028 ANNU.AU.FIN.STMTS 05/16/2001
0028 Teacher Information Form 10/17/2003
0028 Annual Teacher and Curriculum Information Form 02/26/2005
============================================================



REQUIRED RESULTS
============================================================
0028 Annual Teacher and Curriculum Information Form
0002 CA: Environmental Assessment Report
0001 Environmental Compliance Report
============================================================

Any suggestions are welcome. TIA for the help.

 

[synapsevampire]

Group by the ID and in the Report->Edit Selection Formula->Group place:

{table.date} = maximum({table.date},{table.ID})

Your menu may vary based on your software version, please remember to post the essentials in the future.

-k

 

[MJRBIM]

SV -

I can work in CR 8.5 or 10 on this PC.

That formula gets me the most recent record, but sorts them by code number.

ID DESCRIPTION UPDATED_LAST
============================================================
1 Environmental Compliance Report 05/16/2003
2 CA: Environmental Assessment Report 01/30/2004
28 Environmental Compliance Report 02/26/2005
============================================================

I had sort of figured that out, but I still can't figure out how to do the second pass to sort the results by alphabetical description.

Any ideas....?

 

[MJRBIM]

Sorry, bad display of results in the post above....

ID DESCRIPTION UPDATED_LAST
============================================================
1 Environmental Compliance Report 05/16/2003
2 CA: Environmental Assessment Report 01/30/2004
28 Annual Teacher and Curriculum Information Form 02/26/2005
============================================================

 

[lbass]

In 8.5 you could create a SQL expression {%maxdate}:

(select max(AKA.`Updated_Last_Date`) from Table AKA where
AKA.`ID` = Table.`ID`)

Substitute the exact field names for date and ID and the table name for "Table". Then go to the record selection formula area and enter:

{Table.Updated_Last_Date} = {%maxdate}

Then in the report, you can insert a maximum on {table.description}. Then go to topN/group sort and select "maximum of {table.description}" ascending. This will reutrn one row per ID and order them alphabetically.

In 10.0 you would have to create the expression in the "Add Command" area.

-LB

 

[MJRBIM]

OK, that makes sense...I'll check it out on Thursday....

Thanks guys!