Re: selecting distinct records with group max value

[Tom Byars]

Peter, thanks for the speedy reply. I had already looked at that one and it would work fine if the max(Val) within each group was distinct but, there's likely to be many per group equal to the group max(Val). The only way I'd get the first occurence of max(Val) in each group using that example would be to order the result by Val, Fld1, Fld2, Fld3 then select distinct from the result grouped on Fld1 e.g.

select distinct * from
(select t1.* from Tbl left join Tbl t2 on
t2.Fld1=t1.Fld1 and t2.Fld2=t1.Fld2 and t2.Fld3=t1.Fld3 and t2.Val>t1.Val
where t2.Fld1 is null
order by Val desc, Fld1, Fld2, Fld3) Derived
group by Fld1;


however that takes about a hundred times the time it takes my example (which admittedly doesn't neccessarily find the first). The temporary table/subquery examples also have a problem with the ordering.