Greatest N Per Group

[jeremib]

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

 

[jeremib]

I thought I had it, but when I use this, I get "currency not on a record"

SELECT * FROM (
select a.tp_no, c.name, a.state, b.eff_date, a.er_rate as 'Actual', b.er_rate as 'Billed',
(select MAX(e.eff_date)
from "PR_TSUTA" as d
left join CL_SUTA as e on(d.tp_no=e.loc_no)
left join CL_MAST as f on(e.loc_no=f.loc_no)
WHERE d.Tp_No = a.Tp_No AND d.State = a.State) as Max_Eff_Date
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
) as subs
WHERE Max_Eff_Date = Eff_Date
order by Tp_No