SQL query to return only 1 record per group ID

Try this (assuming Group is synonym for Household)

Select * From Table t
Where Age = (Select Max(Age)
             From Table 
             Where GroupId = t.GroupId)

If there are two or more "oldest" people in some household (They all are the same age and there is noone else older), then this will return all of them, not just one at random.

If this is an issue, then you need to add another subquery to return an arbitrary key value for one person in that set.

Select * From Table t
Where Id = 
  (Select Max(Id) Fom Table 
   Where GroupId = t.GroupId
      And Age =
         (Select(Max(Age) From Table
          Where GroupId = t.GroupId))

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.

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