selecting distinct records with group max value

[Tom Byars]

I have a table (Tbl) primary indexed on Fld1, Fld2, Fld3 which has a (non-index) field Val. I want a query to return the first record (in primary index order) within each Fld1 group where Val equals the maximum value within that Fld1 group.

I came up with this

select distinct * from
(select distinct * from Tbl group by Fld1, Val desc) Derived
group by Fld1;

This appears to work but, I'm now thinking it wouldn't work if the natural order of the records was different to primary index order. Is there an easier way?



Edited 1 time(s). Last edit at 05/17/2006 09:41AM by Tom Byars.