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

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

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.

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

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

дополнительная литература

это решение и многие другие описаны в книге антипаттерны SQL: избегая ловушек Программирование Баз Данных

мое простое решение для 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;

вы можете присоединиться к подзапросу, который тянет 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;

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

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

решение 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;

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

С Использованием Обобщенных Табличных Выражений - Общие Табличные Выражения:

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

Не уверен, что 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;

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

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 для того, чтобы создать упорядоченный список конкат, а затем я подстрока только первый.

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

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

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

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

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

пусть имя таблицы будет people

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; 

Если 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
    )

вот как я получаю 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
;

как работает:

• self join to the table
• группы осуществляется co.country = ci.country
• N элементы каждой группы находятся под контролем ) < 1 Так для 3 элементов )
• чтобы получить Макс или мин зависит от: co.id < ci.id
  • co.id
  • co.id > ci.id -мин

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

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

у меня есть простое решение с помощью 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

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`

Я бы не стал использовать группу в качестве имени столбца, так как это зарезервированное слово. Однако следующий 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