On 7/1/07, Jeff Godfrey <[EMAIL PROTECTED]> 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.
select ID, Name, max(version), count(*) Count from table_name group by ID, Name order by ID, Name ... that looks to meet your criteria.