Запрос SQL для возврата только 1 записи на группу ID

Я ищу способ справиться со следующим сценарием. У меня есть таблица базы данных, которую мне нужно возвращать только одну запись для каждого "идентификатора группы", которая содержится в таблице, кроме того запись, которая выбрана в каждой группе должна быть самым старым человеком в домашнем хозяйстве.

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

Итак, в приведенных выше примерах данных мне потребуются идентификаторы 1 и 3, поскольку они являются самыми старыми людьми в каждом идентификаторе группы.

Это запрос к базе данных SQL Server 2005.

Ответы

Ответ 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

или это:

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

Это приведет к возврату не более одной записи на группу даже в случае связей.

См. эту статью в своем блоге для сравнения производительности обоих методов:

Ответ 2

Использование:

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

Так что, если есть 2+ человека с одинаковым возрастом для группы? Было бы лучше сохранить дату рождения, а не возраст - вы всегда можете рассчитать дату рождения для презентации.

Ответ 3

SELECT GroupID, Name, Age
FROM table
INNER JOIN
(
SELECT GroupID, MAX(Age) AS OLDEST
FROM table
) AS OLDESTPEOPLE
ON
table.GroupID = OLDESTPEOPLE.GroupID
AND
table.Age = OLDESTPEOPLE.OLDEST

Ответ 4

Попробуйте это (предположим, что группа является синонимом для домохозяйства)

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

Если в какой-то семье есть два или более "старых" человека (все они одного возраста, а другого нет), то это вернет их всех, а не только случайных.

Если это проблема, вам нужно добавить другой подзапрос, чтобы вернуть произвольное значение ключа для одного человека в этом наборе.

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))