Получить записи с максимальным значением для каждой группы сгруппированных результатов SQL

Question

Как вы получаете строки, которые содержат максимальное значение для каждого сгруппированного набора?

Я видел несколько чрезмерно сложных вариантов этого вопроса, но ни один из них не дал хорошего ответа. Я попытался собрать самый простой пример:

Учитывая приведенную ниже таблицу с столбцами персонажа, группы и возраста, как бы вы получили самого старого человека в каждой группе? (Галстук внутри группы должен дать первый алфавитный результат)

Person | Group | Age
---
Bob  | 1     | 32  
Jill | 1     | 34  
Shawn| 1     | 42  
Jake | 2     | 29  
Paul | 2     | 36  
Laura| 2     | 39  

Требуемый набор результатов:

Shawn | 1     | 42    
Laura | 2     | 39  

Answer 1

Есть очень простой способ сделать это в MySQL:

select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

Это работает, потому что в mysql вам разрешено не агрегировать столбцы без группировки, в этом случае mysql просто возвращает первую строку. Решение состоит в том, чтобы сначала упорядочить данные таким образом, чтобы для каждой группы сначала была указана нужная строка, а затем сгруппировать по столбцам, для которых вы хотите получить значение.

Вы избегаете сложных подзапросов, которые пытаются найти max() и т. д., а также проблемы с возвратом нескольких строк, когда существует более одной строки с одинаковым максимальным значением (как и другие ответы)

Примечание: это решение только для mysql. Все другие базы данных, которые я знаю, будут выдавать синтаксическую ошибку SQL с сообщением "неагрегированные столбцы не перечислены в предложении group by" или аналогичными. Поскольку это решение использует недокументированное поведение, более осторожный может захотеть включить тест, чтобы утверждать, что он продолжает работать, если будущая версия MySQL изменит это поведение.

Обновление версии 5.7:

Начиная с версии 5.7, sql-mode настройка включает в себя ONLY_FULL_GROUP_BY по умолчанию, поэтому, чтобы это работало, у вас не должно быть этой опции (отредактируйте файл опции для сервера, чтобы удалить эту настройку).

Answer 2

Правильное решение:

SELECT o.*
FROM `Persons` o                    # 'o' from 'oldest person in group'
  LEFT JOIN `Persons` b             # 'b' from 'bigger age'
      ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL                 # bigger age not found

Как это устроено:

Это соответствует каждой строке из o со всеми рядами из b с одинаковым значением в столбце Group и большее значение в столбце Age, Любая строка из o не имеет максимального значения своей группы в столбце Age будет соответствовать один или несколько строк из b,

LEFT JOIN позволяет сопоставить самого старого человека в группе (включая лиц, которые одиноки в своей группе) с рядом, полным NULL с из b ("нет самого большого возраста в группе").
С помощью INNER JOIN делает эти строки не совпадающими, и они игнорируются.

WHERE предложение сохраняет только те строки, которые имеют NULL s в полях, извлеченных из b, Это самые старые люди в каждой группе.

Дальнейшие чтения

Это решение и многие другие объясняются в книге " Антипаттерны SQL: предотвращение ловушек программирования баз данных".

Answer 3

Вы можете присоединиться к подзапросу, который тянет MAX(Group) а также Age, Этот метод является переносимым для большинства СУБД.

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT `Group`, MAX(Age) AS max_age
    FROM yourTable
    GROUP BY `Group`
) t2
    ON t1.`Group` = t2.`Group` AND t1.Age = t2.max_age;

Answer 4

Мое простое решение для SQLite (и, вероятно, MySQL):

SELECT *, MAX(age) FROM mytable GROUP BY `Group`;

Однако это не работает в PostgreSQL и, возможно, на некоторых других платформах.

В PostgreSQL вы можете использовать предложение DISTINCT ON:

SELECT DISTINCT ON ("group") * FROM "mytable" ORDER BY "group", "age" DESC;

Answer 5

Не уверен, что в MySQL есть функция row_number. Если это так, вы можете использовать его, чтобы получить желаемый результат. На SQL Server вы можете сделать что-то похожее на:

CREATE TABLE p
(
 person NVARCHAR(10),
 gp INT,
 age INT
);
GO
INSERT  INTO p
VALUES  ('Bob', 1, 32);
INSERT  INTO p
VALUES  ('Jill', 1, 34);
INSERT  INTO p
VALUES  ('Shawn', 1, 42);
INSERT  INTO p
VALUES  ('Jake', 2, 29);
INSERT  INTO p
VALUES  ('Paul', 2, 36);
INSERT  INTO p
VALUES  ('Laura', 2, 39);
GO

SELECT  t.person, t.gp, t.age
FROM    (
         SELECT *,
                ROW_NUMBER() OVER (PARTITION BY gp ORDER BY age DESC) row
         FROM   p
        ) t
WHERE   t.row = 1;

Answer 6

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

      SELECT o.*
FROM `Persons` o 
  LEFT JOIN `Persons` b 
      ON o.Group = b.Group AND o.Age < b.Age
  LEFT JOIN `Persons` c 
      ON o.Group = c.Group AND o.Age = c.Age and o.id < c.id
WHERE b.Age is NULL and c.id is null

Answer 7

Используя метод ранжирования.

SELECT @rn :=  CASE WHEN @prev_grp <> groupa THEN 1 ELSE @rn+1 END AS rn,  
   @prev_grp :=groupa,
   person,age,groupa  
FROM   users,(SELECT @rn := 0) r        
HAVING rn=1
ORDER  BY groupa,age DESC,person

Answer 8

Мое решение работает только в том случае, если вам нужно извлечь только один столбец, однако для моих нужд было найдено лучшее решение с точки зрения производительности (оно использует только один запрос!):

SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),',',1) AS xyz,
   column_z
FROM table_name
GROUP BY column_z;

Он использует GROUP_CONCAT для создания упорядоченного списка конкатов, а затем я подстроку только к первому.

Answer 9

Я бы не использовал Group в качестве имени столбца, так как это зарезервированное слово. Однако следующий SQL будет работать.

SELECT a.Person, a.Group, a.Age FROM [TABLE_NAME] a
INNER JOIN 
(
  SELECT `Group`, MAX(Age) AS oldest FROM [TABLE_NAME] 
  GROUP BY `Group`
) b ON a.Group = b.Group AND a.Age = b.oldest

Answer 10

Решение Axiac - это то, что лучше всего сработало для меня. Однако у меня была дополнительная сложность: вычисленное "максимальное значение", полученное из двух столбцов.

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

Мне пришлось выполнить левое соединение два раза, чтобы получить такое поведение:

SELECT o1.* WHERE
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o1
LEFT JOIN
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o2
ON o1.Group = o2.Group AND o1.Height < o2.Height 
WHERE o2.Height is NULL;

Надеюсь это поможет! Я думаю, что должен быть лучший способ сделать это, хотя...

Answer 11

В Oracle ниже запрос может дать желаемый результат.

SELECT group,person,Age,
  ROWNUMBER() OVER (PARTITION BY group ORDER BY age desc ,person asc) as rankForEachGroup
  FROM tablename where rankForEachGroup=1

Answer 12

У меня есть простое решение с помощью WHERE IN

SELECT a.* FROM `mytable` AS a    
WHERE a.age IN( SELECT MAX(b.age) AS age FROM `mytable` AS b GROUP BY b.group )    
ORDER BY a.group ASC, a.person ASC

Answer 13

Использование CTE - общие табличные выражения:

WITH MyCTE(MaxPKID, SomeColumn1)
AS(
SELECT MAX(a.MyTablePKID) AS MaxPKID, a.SomeColumn1
FROM MyTable1 a
GROUP BY a.SomeColumn1
  )
SELECT b.MyTablePKID, b.SomeColumn1, b.SomeColumn2 MAX(b.NumEstado)
FROM MyTable1 b
INNER JOIN MyCTE c ON c.MaxPKID = b.MyTablePKID
GROUP BY b.MyTablePKID, b.SomeColumn1, b.SomeColumn2

--Note: MyTablePKID is the PrimaryKey of MyTable

Answer 14

Вот как я получаю N max строк на группу в MySQL

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country AND co.id < ci.id
) < 1
;

как это устроено:

• самостоятельно присоединиться к столу
• группы сделаны co.country = ci.country
• N элементов в группе контролируются ) < 1 так что за 3 элемента -) < 3
• получить максимум или мин зависит от: co.id < ci.id
  • co.id
  • co.id > ci.id - мин

Полный пример здесь:

mysql выбрать n максимальных значений для группы

Answer 15

Вы также можете попробовать

SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;

Answer 16

with CTE as 
(select Person, 
[Group], Age, RN= Row_Number() 
over(partition by [Group] 
order by Age desc) 
from yourtable)`


`select Person, Age from CTE where RN = 1`

Answer 17

Если ID(и все coulmns) необходимы из mytable

SELECT
    *
FROM
    mytable
WHERE
    id NOT IN (
        SELECT
            A.id
        FROM
            mytable AS A
        JOIN mytable AS B ON A. GROUP = B. GROUP
        AND A.age < B.age
    )

Answer 18

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

Источник: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

SELECT person, group,
    GROUP_CONCAT(
        DISTINCT age
        ORDER BY age DESC SEPARATOR ', follow up: '
    )
FROM sql_table
GROUP BY group;

Answer 19

Пусть имя таблицы будет людьми

select O.*              -- > O for oldest table
from people O , people T
where O.grp = T.grp and 
O.Age = 
(select max(T.age) from people T where O.grp = T.grp
  group by T.grp)
group by O.grp; 

Answer 20

      SELECT o.*
FROM `Persons` o                   
  LEFT JOIN `Persons` b            
      ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL  
group by o.Group