I have a table with 3 fields - policy number, exp_mod, eff_date. There are many records for each policy number.

I need to get the exp_mod for each policy with the latest eff_date.

If I use the following code I get two or three record as opposed to the exp mod associated with the lastest date

Select policy,exp_mod,max(eff_date) from table exp_mod_table ;
group by policy,exp_mod into cursor xxx

I assume I could order the results in descending order by eff_date and then select the first record for each policy, but there surely have to be a better way.


Any help would be appreciated.
Gaylen