SQL query to return only 1 record per group ID

Question

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.

Answer 1

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:

• SQL Server: Selecting records holding group-wise maximum

Answer 2

Use:

SELECT DISTINCT        t.groupid,        t.name   FROM TABLE t   JOIN (SELECT t.groupid,                MAX(t.age) 'max_age'           FROM TABLE t       GROUP BY t.groupid) x ON x.groupid = t.groupid                            AND x.max_age = t.age 

So what if there's 2+ people with the same age for a group? It'd be better to store the birthdate rather than age - you can always calculate the age for presentation.