Puneet, I'm assuming you meant to say "where rank is the highest" rather than title, as that's what your example shows.
Here's the syntax: select * from table a where rank = (select max(rank) from table b where b.id = a.id) - Jeff -----Original Message----- From: P Kishor [mailto:[EMAIL PROTECTED] Sent: Monday, January 28, 2008 10:42 PM To: sqlite-users@sqlite.org Subject: [sqlite] find the highest rank per group 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] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
