Using Pervasive SQL, I have a result set:
Tp_No Name State Eff_Date Actual Billed
1006 ABC TN 2006-07-01 .1 .5
1006 ABC TN 2008-02-15 .27 .6
1006 ABC TN 2010-09-01 .37 .7
1022 Widget TN 2006-07-01 .1 .5
1022 Widget TN 2007-02-22 .27 .6
1022 Widget TN 2009-01-01 .37 .7
1022 Widget TN 2010-11-11 .38 .71
What I want is the row for each Client, Company, and State where the date is MAX:
Tp_No Name State Eff_Date Actual Billed
1006 ABC TN 2010-09-01 .37 .7
1022 Widget TN 2010-11-11 .38 .71
What makes it a little more difficult is the fact that the original result set is the results of a query, not just straight from a table.
select a.tp_no, c.name, a.state, b.eff_date, a.er_rate as 'Actual', b.er_rate as 'Billed'
from "PR_TSUTA" as a
left join CL_SUTA as b on(a.tp_no=b.loc_no)
left join CL_MAST as c on(b.loc_no=c.loc_no)
where c.yn_17 = 'A' and a.er_rate != b.er_rate
order by a.tp_no
Thanks in advance