On Sun, 2007-07-01 at 16:31 -0500, Jeff Godfrey wrote: > Hi All, > > Given the following sample data... > > ID Name Version > --- ----- ------- > 1 name1 0.9 > 2 name1 1.0 > 3 name2 1.2 > 4 name3 1.0 > 5 name3 1.7 > 6 name3 1.5 > > I need to create a query that will group the data together by Name, but for > each group will return the record with the highest version number and a count > of the items in the group. So, with the above data, I'd expect this as > output: > > ID Name Version Count > --- ----- ------- ----- > 2 name1 1.0 2 > 3 name2 1.2 1 > 5 name3 1.7 3 > > Thanks for any assistance.
How about: SELECT tbl.id, grp.name, grp.c, grp.v FROM (SELECT name, count(*) AS c, max(version) AS v FROM tbl GROUP BY name ) AS grp, tbl WHERE grp.name = tbl.name AND grp.version = tbl.version; I think the question only makes sense if the combination of name and version are unique in the table. Dan. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
