Obtener registros con valor máximo para cada grupo de resultados SQL agrupados

La solución correcta es:

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

Cómo funciona:

Coincide con cada fila de o con todas las filas de b tener el mismo valor en la columna Group y un mayor valor en la columna Age. Cualquier fila de o que no tenga el valor máximo de su grupo en la columna Age coincidirá con una o más filas de b.

El LEFT JOIN hace que coincida con la persona más antigua del grupo (incluidas las personas que están solas en su grupo) con una fila llena de NULLs de b ('no mayor edad en el grupo").
Usar INNER JOIN hace que estas filas no coincidan y se ignoran.

La cláusula WHERE mantiene solo las filas que tienen NULL s en los campos extraídos de b. Son las personas de más edad de cada grupo.

Otras lecturas

Esta solución y muchas otras se explican en el libro SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Mi solución simple para SQLite (y probablemente MySQL):

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

Sin embargo, no funciona en PostgreSQL y tal vez en otras plataformas.

En PostgreSQL puede usar DISTINCT EN la cláusula:

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

Puede unirse contra una subconsulta que tire de MAX(Group) y Age. Este método es portátil a través de la mayoría de RDBMS.

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;

Utilizando el método de clasificación.

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

La solución de Axiac es lo que funcionó mejor para mí al final. Sin embargo, tenía una complejidad adicional: un "valor máximo" calculado, derivado de dos columnas.

Usemos el mismo ejemplo: Me gustaría la persona más antigua de cada grupo. Si hay personas que son igualmente viejos, tomar la persona más alta.

Tuve que realizar el join izquierdo dos veces para obtener este comportamiento:

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;

Espero que esto ayude! Supongo que debería haber una mejor manera de hacer esto...

Usando CTEs-Expresiones de Tabla Comunes:

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

No estoy seguro si MySQL tiene la función row_number. Si es así, puede usarlo para obtener el resultado deseado. En SQL Server se puede hacer algo similar a:

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;

Mi solución funciona solo si necesita recuperar solo una columna, sin embargo para mis necesidades fue la mejor solución encontrada en términos de rendimiento (utiliza solo una sola consulta!):

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

Usa GROUP_CONCAT para crear una lista concat ordenada y luego substring a solo la primera.

También puedes probar

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

Este método tiene la ventaja de permitirle clasificar por una columna diferente, y no destrozar los otros datos. Es bastante útil en una situación en la que está tratando de enumerar los pedidos con una columna para los artículos, enumerando primero los más pesados.

Fuente: 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;

Que el nombre de la tabla sea personas

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; 

Si se necesita ID(y todos los coulmns) de 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
    )

Así es como obtengo las N filas máximas por grupo en 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
;

Cómo funciona:

• auto unirse a la tabla
• los grupos son hechos por co.country = ci.country
• N elementos por grupo son controlados por ) < 1 así que para 3 elementos -)
• para obtener max o min depende de: co.id < ci.id
  • co.id
  • co.id > ci.id -min

Ejemplo completo aquí:

Mysql selecciona n valores máximos por grupo

Tengo una solución simple usando 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`

No usaría Grupo como nombre de columna ya que es palabra reservada. Sin embargo, después de SQL funcionaría.

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