Search code examples
sqlsql-serversql-server-2005

SQL query to return only 1 record per group ID

I'm looking for a way to handle the following scenario. I have a database table that I need to return only one record for each "group id" that is contained within the table, furthermore the record that is selected within each group should be the oldest person in the household.

ID   Group ID   Name               Age
1   134        John Bowers        37
2   134        Kerri Bowers       33
3   135        John Bowers        44
4   135        Shannon Bowers     42

So in the sample data provided above I would need ID 1 and 3 returned, as they are the oldest people within each group id.

This is being queried against a SQL Server 2005 database.

Solution 
  • SELECT  t.*
FROM    (
        SELECT  DISTINCT groupid
        FROM    mytable
        ) mo
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    mytable mi
        WHERE   mi.groupid = mo.groupid
        ORDER BY
                age DESC
        ) t

    or this:

    SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY groupid ORDER BY age DESC) rn
        FROM    mytable
        ) x
WHERE   x.rn = 1

    This will return at most one record per group even in case of ties.

    See this article in my blog for performance comparisons of both methods: