Get records with max value for each group of grouped SQL results

Question

How do you get the rows that contain the max value for each grouped set    I ve seen some overly-complicated variations on this question  and none with a good answer  I ve tried to put together the simplest possible example   Given a table like that below  with person  group  and age columns  how would you get the oldest person in each group   A tie within a group should give the first alphabetical result   Person   Group   Age --- Bob    1       32   Jill   1       34   Shawn  1       42   Jake   2       29   Paul   2       36   Laura  2       39     Desired result set     Shawn   1       42     Laura   2       39

User · Answer

In Oracle below query can give the desired result   SELECT group person Age    ROWNUMBER   OVER  PARTITION BY group ORDER BY age desc  person asc  as rankForEachGroup   FROM tablename where rankForEachGroup 1

User · Answer

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

User · Answer

Using CTEs - Common Table Expressions   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

User · Answer

This method has the benefit of allowing you to rank by a different column  and not trashing the other data  It s quite useful in a situation where you are trying to list orders with a column for items  listing the heaviest first   Source  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

User · Answer

Not sure if MySQL has row number function  If so you can use it to get the desired result  On SQL Server you can do something similar to   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

User · Answer

axiac s solution is what worked best for me in the end  I had an additional complexity however  a calculated  max value   derived from two columns   Let s use the same example  I would like the oldest person in each group  If there are people that are equally old  take the tallest person   I had to perform the left join two times to get this behavior   SELECT o1   WHERE      SELECT o       FROM  Persons  o     LEFT JOIN  Persons  b     ON o Group   b Group AND o Age  lt  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  lt  b Age     WHERE b Age is NULL  o2 ON o1 Group   o2 Group AND o1 Height  lt  o2 Height  WHERE o2 Height is NULL    Hope this helps  I guess there should be better way to do this though

User · Answer

If ID and all coulmns  is needed from 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  lt  B age

User · Answer

You can also try  SELECT   FROM mytable WHERE age IN  SELECT MAX age  FROM mytable GROUP BY  Group

User · Answer

let the table name be people  select O                --  gt  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

User · Answer

My simple solution for SQLite  and probably MySQL    SELECT    MAX age  FROM mytable GROUP BY  Group     However it doesn t work in PostgreSQL and maybe some other platforms   In PostgreSQL you can use DISTINCT ON clause   SELECT DISTINCT ON   group     FROM  mytable  ORDER BY  group    age  DESC

User · Answer

My solution works only if you need retrieve only one column  however for my needs was the best solution found in terms of performance  it use only one single query     SELECT SUBSTRING INDEX GROUP CONCAT column x ORDER BY column y      1  AS xyz     column z FROM table name GROUP BY column z    It use GROUP CONCAT in order to create an ordered concat list and then I substring to only the first one

User · Answer

There s a super-simple way to do this in mysql  select    from  select   from mytable order by  Group   age desc  Person  x group by  Group   This works because in mysql you re allowed to not aggregate non-group-by columns  in which case mysql just returns the first row  The solution is to first order the data such that for each group the row you want is first  then group by the columns you want the value for  You avoid complicated subqueries that try to find the max   etc  and also the problems of returning multiple rows when there are more than one with the same maximum value  as the other answers would do  Note  This is a mysql-only solution  All other databases I know will throw an SQL syntax error with the message  quot non aggregated columns are not listed in the group by clause quot  or similar  Because this solution uses undocumented behavior  the more cautious may want to include a test to assert that it remains working should a future version of MySQL change this behavior  Version 5 7 update  Since version 5 7  the sql-mode setting includes ONLY FULL GROUP BY by default  so to make this work you must not have this option  edit the option file for the server to remove this setting

User · Answer

I would not use Group as column name since it is reserved word  However following SQL would work   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

User · Answer

The correct solution is   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  lt  b Age WHERE b Age is NULL                   bigger age not found   How it works   It matches each row from o with all the rows from b having the same value in column Group and a bigger value in column Age  Any row from o not having the maximum value of its group in column Age will match one or more rows from b   The LEFT JOIN makes it match the oldest person in group  including the persons that are alone in their group  with a row full of NULLs from b   no biggest age in the group    Using INNER JOIN makes these rows not matching and they are ignored   The WHERE clause keeps only the rows having NULLs in the fields extracted from b  They are the oldest persons from each group   Further readings  This solution and many others are explained in the book SQL Antipatterns  Avoiding the Pitfalls of Database Programming

User · Answer

You can join against a subquery that pulls the MAX Group  and Age  This method is portable across most 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

User · Answer

Using ranking method  SELECT  rn     CASE WHEN  prev grp  lt  gt  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  This sql can be explained as below   select   from users   select  rn    0  r order by groupa  age desc  person   prev grp is null   rn     CASE WHEN  prev grp  lt  gt  groupa THEN 1 ELSE  rn 1 END this is a three operator expression like this  rn   1 if prev grp    groupa else rn rn 1  having rn 1 filter out the row you need

User · Answer

Improving axiac s solution to avoid selecting multiple rows per group while also allowing for use of indexes SELECT o   FROM  Persons  o    LEFT JOIN  Persons  b        ON o Group   b Group AND o Age  lt  b Age   LEFT JOIN  Persons  c        ON o Group   c Group AND o Age   c Age and o id  lt  c id WHERE b Age is NULL and c id is null

User · Answer

This is how I m getting the N max rows per group in 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  lt  ci id    lt  1     how it works    self join to the table groups are done by co country   ci country N elements per group are controlled by    lt  1 so for 3 elements -    lt  3 to get max or min depends on  co id  lt  ci id   co id  lt  ci id - max co id   ci id - min    Full example here   mysql select n max values per group

[mysql] Get records with max value for each group of grouped SQL results

Examples related to mysql

Examples related to sql

Examples related to greatest-n-per-group