SELECT tb.cKey, tb.cValue FROM tblValues tb ; JOIN ( ; SELECT cKey, MAX(nPriority) MaxPriority ; FROM tblValues ; GROUP BY 1 ; ) mt ; ON mt.cKey = tb.cKey ; AND mt.MaxPriority = tb.nPriority * Another one that uses correlated subquery and works in VFP8 and earelier SELECT tb.cKey, tb.cValue FROM tblValues tb ; WHERE tb.nPriority IN ; ( SELECT MAX(nPriority) ; FROM tblValues tb2 ; WHERE tb2.cKey = tb.cKey )>Here's a puzzle for you SQL masters:
>create table tblValues ( ; > cKey c( 32 ), ; > cValue c( 16 ), ; > nPriority i ; >) >>
>apple, red, 8 >apple, yellow, 2 >apple, brown, 0 > >corn, green, 4 >corn, yellow, 9 >corn, brown, 2 > >eggs, pink, 6 >eggs, purple, 7 >eggs, white, 8 >>
>apple, red >corn, yellow >eggs, white >>