Here's a puzzle for you SQL masters:

I have the following table:

create table tblValues ( ;
  cKey       c( 32 ), ;
  cValue     c( 16 ), ;
  nPriority  i        ;
)

This table has many records with the same cKey value, but with differing values for nPriority. I would like to run a query that returns the cKey, cValue records that have the highest nPriority values for each set of matching cKey's.

Example data (remove blank records when importing)

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

Here's the result set I'm looking for:

apple, red
corn,  yellow
eggs,  white

The way I do this now is to create an index on cKey + padl( nPriority ), set order descending, then scan the source table inserting the first unique cKey record I find and insert it into a cursor.

Seems to me there may be a more effective way to accomplish this?

Any takers?

Malcolm