[sqlite] find the highest rank per group

[P Kishor]

I have

id, name, .., title, rank
1, a, .., foo, 5
1, a, .., bar, 4
1, a, .., bar, 7
2, b, .., baz, 6
2, b, .., qux, 9

and so on

I want

1, a, .., bar, 7
2, b, .., qux, 9

that is, all the rows for each name where title is the highest.

SELECT id, name, .., title, MAX(rank)
FROM table
GROUP BY id, name, .., title

doesn't cut it as it finds

1, a, .., foo, 5
1, a, .., bar, 7
2, b, .., qux, 9

Instead, I want only one occurrence of "name" What would be the syntax for this?

Thanks,

Puneet.

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------