On 1/28/08, Fowler, Jeff <[EMAIL PROTECTED]> wrote: > Puneet, > > I'm assuming you meant to say "where rank is the highest" rather than > title, as that's what your example shows.
indeed. > > Here's the syntax: > select * from table a > where rank = (select max(rank) from table b where b.id = a.id) Many thanks. That works. > > - 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] > ------------------------------------------------------------------------ > ----- > > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
