SQL select only rows with max value on a column

Question

I have this table for documents  simplified version here     ------ ------- --------------------------------------    id     rev     content                                 ------ ------- --------------------------------------    1      1                                                2      1                                                1      2                                                1      3                                               ------ ------- --------------------------------------    How do I select one row per id and only the greatest rev  With the above data  the result should contain two rows   1  3       and  2  1       I m using MySQL   Currently I use checks in the while loop to detect and over-write old revs from the resultset  But is this the only method to achieve the result  Isn t there a SQL solution   Update As the answers suggest  there is a SQL solution  and here a sqlfiddle demo    Update 2 I noticed after adding the above sqlfiddle  the rate at which the question is upvoted has surpassed the upvote rate of the answers  That has not been the intention  The fiddle is based on the answers  especially the accepted answer

User · Answer

None of these answers have worked for me   This is what worked for me   with score as  select max score up  from history  select history   from score  history where history score up   score max

User · Answer

A third solution I hardly ever see mentioned is MySQL specific and looks like this   SELECT id  MAX rev  AS rev    0 SUBSTRING INDEX GROUP CONCAT numeric content ORDER BY rev DESC        1  AS numeric content FROM t1 GROUP BY id   Yes it looks awful  converting to string and back etc   but in my experience it s usually faster than the other solutions  Maybe that just for my use cases  but I have used it on tables with millions of records and many unique ids  Maybe it s because MySQL is pretty bad at optimizing the other solutions  at least in the 5 0 days when I came up with this solution    One important thing is that GROUP CONCAT has a maximum length for the string it can build up  You probably want to raise this limit by setting the group concat max len variable  And keep in mind that this will be a limit on scaling if you have a large number of rows   Anyway  the above doesn t directly work if your content field is already text  In that case you probably want to use a different separator  like  0 maybe  You ll also run into the group concat max len limit quicker

User · Answer

I like to do this by ranking the records by some column  In this case  rank rev values grouped by id  Those with higher rev will have lower rankings  So highest rev will have ranking of 1   select id  rev  content from   select      rowNum    if  prevValue   id   rowNum 1  1  as row num      id  rev  content       prevValue    id   from     select id  rev  content from YOURTABLE order by id asc  rev desc  TEMP      select  rowNum    1 from DUAL  X      select  prevValue    -1 from DUAL  Y  TEMP where row num   1    Not sure if introducing variables makes the whole thing slower  But at least I m not querying YOURTABLE twice

User · Answer

Unique Identifiers   Yes   Unique identifiers  One of the best ways to develop a MySQL DB is to have each id AUTOINCREMENT  Source MySQL com    This allows a variety of advantages  too many to cover here   The problem with the question is that its example has duplicate ids   This disregards these tremendous advantages of unique identifiers  and at the same time  is confusing to those familiar with this already  Cleanest Solution DB Fiddle Newer versions of MySQL come with ONLY FULL GROUP BY enabled by default  and many of the solutions here will fail in testing with this condition  Even so  we can simply select DISTINCT someuniquefield  MAX  whateverotherfieldtoselect       somethirdfield    etc   and have no worries understanding the result or how the query works   SELECT DISTINCT t1 id  MAX t1 rev   MAX t2 content  FROM Table1 AS t1 JOIN Table1 AS t2 ON t2 id   t1 id AND t2 rev         SELECT MAX rev  FROM Table1 t3 WHERE t3 id   t1 id   GROUP BY t1 id    SELECT DISTINCT Table1 id  max Table1 rev   max Table2 content    Return DISTINCT somefield  MAX   some otherfield  the last MAX   is redundant  because I know it s just one row  but it s required by the query  FROM Employee   Table searched on  JOIN Table1 AS Table2 ON Table2 rev   Table1 rev   Join the second table on the first  because  we need to get the max table1 rev  s comment  GROUP BY Table1 id  Force the top-sorted  Salary row of each employee to be the returned result   Note that since  quot content quot  was  quot     quot  in OP s question  there s no way to test that this works   So  I changed that to  quot   a quot    quot   b quot   so  we can actually now see that the results are correct  id  max Table1 rev  max Table2 content  1   3     d 2   1     b  Why is it clean   DISTINCT    MAX    etc   all make wonderful use of MySQL indices   This will be faster   Or  it will be much faster  if you have indexing  and you compare it to a query that looks at all rows  Original Solution With ONLY FULL GROUP BY disabled  we can use still use GROUP BY  but then we are only using it on the Salary  and not the id  SELECT   FROM      SELECT       FROM Employee     ORDER BY Salary DESC  AS employeesub GROUP BY employeesub Salary    SELECT     Return all fields  FROM Employee   Table searched on   SELECT       subquery   Return all people  sorted by Salary  GROUP BY employeesub Salary  Force the top-sorted  Salary row of each employee to be the returned result   Unique-Row Solution Note the Definition of a Relational Database   quot Each row in a table has its own unique key  quot   This would mean that  in the question s example  id would have to be unique  and in that case  we can just do   SELECT   FROM Employee WHERE Employee id   12345 ORDER BY Employee Salary DESC LIMIT 1  Hopefully this is a solution that solves the problem and helps everyone better understand what s happening in the DB

User · Answer

I used the below to solve a problem of my own  I first created a temp table and inserted the max rev value per unique id   CREATE TABLE  temp1       id varchar 20        rev int   INSERT INTO  temp1 SELECT a id  MAX a rev  as rev FROM                SELECT id  content  SUM rev  as rev         FROM YourTable         GROUP BY id  content       as a  GROUP BY a id ORDER BY a id   I then joined these max values   temp1  to all of the possible id content combinations  By doing this  I naturally filter out the non-maximum id content combinations  and am left with the only max rev values for each   SELECT a id  a rev  content FROM  temp1 as a LEFT JOIN               SELECT id  content  SUM rev  as rev         FROM YourTable         GROUP BY id  content       as b on a id   b id and a rev   b rev GROUP BY a id  a rev  b content ORDER BY a id

User · Answer

At first glance     All you need is a GROUP BY clause with the MAX aggregate function   SELECT id  MAX rev  FROM YourTable GROUP BY id   It s never that simple  is it   I just noticed you need the content column as well   This is a very common question in SQL  find the whole data for the row with some max value in a column per some group identifier  I heard that a lot during my career  Actually  it was one the questions I answered in my current job s technical interview    It is  actually  so common that StackOverflow community has created a single tag just to deal with questions like that  greatest-n-per-group   Basically  you have two approaches to solve that problem   Joining with simple group-identifier  max-value-in-group Sub-query  In this approach  you first find the group-identifier  max-value-in-group  already solved above  in a sub-query  Then you join your table to the sub-query with equality on both group-identifier and max-value-in-group   SELECT a id  a rev  a contents FROM YourTable a INNER JOIN       SELECT id  MAX rev  rev     FROM YourTable     GROUP BY id   b ON a id   b id AND a rev   b rev   Left Joining with self  tweaking join conditions and filters  In this approach  you left join the table with itself  Equality goes in the group-identifier  Then  2 smart moves     The second join condition is having left side value less than right value When you do step 1  the row s  that actually have the max value will have NULL in the right side  it s a LEFT JOIN  remember    Then  we filter the joined result  showing only the rows where the right side is NULL    So you end up with   SELECT a   FROM YourTable a LEFT OUTER JOIN YourTable b     ON a id   b id AND a rev  lt  b rev WHERE b id IS NULL    Conclusion  Both approaches bring the exact same result    If you have two rows with max-value-in-group for group-identifier  both rows will be in the result in both approaches   Both approaches are SQL ANSI compatible  thus  will work with your favorite RDBMS  regardless of its  flavor    Both approaches are also performance friendly  however your mileage may vary  RDBMS  DB Structure  Indexes  etc    So when you pick one approach over the other  benchmark  And make sure you pick the one which make most of sense to you

User · Answer

This solution makes only one selection from YourTable  therefore it s faster  It works only for MySQL and SQLite for SQLite remove DESC  according to test on sqlfiddle com  Maybe it can be tweaked to work on other languages which I am not familiar with   SELECT   FROM   SELECT          FROM   SELECT 1 as id  1 as rev   content1  as content               UNION               SELECT 2  1   content2                UNION               SELECT 1  2   content3                UNION               SELECT 1  3   content4                as YourTable        ORDER BY id  rev DESC      as YourTable GROUP BY id

User · Answer

Something like this  SELECT yourtable id  rev  content FROM yourtable INNER JOIN       SELECT id  max rev  as maxrev     FROM yourtable     GROUP BY id   AS child ON  yourtable id   child id  AND  yourtable rev   maxrev

User · Answer

Yet another solution is to use a correlated subquery   select yt id  yt rev  yt contents     from YourTable yt     where rev             select max rev  from YourTable st where yt id st id    Having an index on  id rev  renders the subquery almost as a simple lookup     Following are comparisons to the solutions in  AdrianCarneiro s answer  subquery  leftjoin   based on MySQL measurements with InnoDB table of  1million records  group size being  1-3   While for full table scans subquery leftjoin correlated timings relate to each other as 6 8 9  when it comes to direct lookups or batch  id in  1 2 3    subquery is much slower then the others  Due to rerunning the subquery   However I couldnt differentiate between leftjoin and correlated solutions in speed   One final note  as leftjoin creates n  n 1  2 joins in groups  its performance can be heavily affected by the size of groups

User · Answer

here is another solution hope it will help someone      Select a id   a rev  a content from Table1 a inner join   SELECT id  max rev  rev FROM Table1 GROUP BY id  x on x id  a id and x rev  a rev

User · Answer

My preference is to use as little code as possible     You can do it using IN try this   SELECT    FROM t1 WHERE  id rev  IN    SELECT id  MAX rev    FROM t1   GROUP BY id     to my mind it is less complicated    easier to read and maintain

User · Answer

Another manner to do the job is using MAX   analytic function in OVER PARTITION clause  SELECT t     FROM           SELECT id            rev            contents            MAX rev  OVER  PARTITION BY id  as max rev       FROM YourTable       t   WHERE t rev   t max rev    The other ROW NUMBER   OVER PARTITION solution already documented in this post is  SELECT t     FROM           SELECT id            rev            contents            ROW NUMBER   OVER  PARTITION BY id ORDER BY rev DESC  rank       FROM YourTable       t   WHERE t rank   1    This 2 SELECT work well on Oracle 10g   MAX   solution runs certainly FASTER that ROW NUMBER   solution because MAX   complexity is O n  while ROW NUMBER   complexity is at minimum O n log n   where n represent the number of records in table

User · Answer

NOT mySQL  but for other people finding this question and using SQL  another way to resolve the greatest-n-per-group problem is using Cross Apply in MS SQL  WITH DocIds AS  SELECT DISTINCT id FROM docs   SELECT d2 id  d2 rev  d2 content FROM DocIds d1 CROSS APPLY     SELECT Top 1   FROM docs d   WHERE d id   d1 id   ORDER BY rev DESC   d2   Here s an example in SqlFiddle

User · Answer

You can make the select without a join when you combine the rev and id into one maxRevId value for MAX   and then split it back to original values   SELECT maxRevId  amp    1  lt  lt  32  - 1  as id  maxRevId  gt  gt  32 AS rev FROM  SELECT MAX   rev  lt  lt  32    id   AS maxRevId       FROM YourTable       GROUP BY id  x    This is especially fast when there is a complex join instead of a single table  With the traditional approaches the complex join would be done twice   The above combination is simple with bit functions when rev and id are INT UNSIGNED  32 bit  and combined value fits to BIGINT UNSIGNED  64 bit   When the id  amp  rev are larger than 32-bit values or made of multiple columns  you need combine the value into e g  a binary value with suitable padding for MAX

User · Answer

Explanation  This is not pure SQL  This will use the SQLAlchemy ORM   I came here looking for SQLAlchemy help  so I will duplicate Adrian Carneiro s answer with the python SQLAlchemy version  specifically the outer join part   This query answers the question of       Can you return me the records in this group of records  based on same id  that have the highest version number      This allows me to duplicate the record  update it  increment its version number  and have the copy of the old version in such a way that I can show change over time   Code  MyTableAlias   aliased MyTable  newest records   appdb session query MyTable  select from join      MyTable       MyTableAlias       onclause and           MyTable id    MyTableAlias id          MyTable version int  lt  MyTableAlias version int            isouter True         filter      MyTableAlias id     None    all     Tested on a PostgreSQL database

User · Answer

I think  You want this   select   from docs where  id  rev  IN  select id  max rev  as rev from docs group by id order by id      SQL Fiddle   Check here

User · Answer

I like to use a NOT EXIST-based solution for this problem   SELECT    id     rev   -- you can select other columns here FROM YourTable t WHERE NOT EXISTS      SELECT   FROM YourTable t WHERE t id   id AND rev  gt  t rev     This will select all records with max value within the group and allows you to select other columns

User · Answer

I would use this   select t   from test as t join     select max rev  as rev     from test     group by id  as o on o rev   t rev   Subquery SELECT is not too eficient maybe  but in JOIN clause seems to be usable  I m not an expert in optimizing queries  but I ve tried at MySQL  PostgreSQL  FireBird and it does work very good   You can use this schema in multiple joins and with WHERE clause  It is my working example  solving identical to yours problem with table  firmy     select   from platnosci as p join firmy as f on p id rel firmy   f id rel join  select max id obj  as id obj       from firmy       group by id rel  as o on o id obj   f id obj and p od  gt   2014-03-01    It is asked on tables having teens thusands of records  and it takes less then 0 01 second on really not too strong machine   I wouldn t use IN clause  as it is mentioned somewhere above   IN is given to use with short lists of constans  and not as to be the query filter built on subquery  It is because subquery in IN is performed for every scanned record which can made query taking very loooong time

User · Answer

Here is a nice way of doing that  Use following code    with temp as     select count field1  as summ   field1 from table name group by field1   select   from temp where summ    select max summ  from temp

User · Answer

I am flabbergasted that no answer offered SQL window function solution   SELECT a id  a rev  a contents   FROM  SELECT id  rev  contents                 ROW NUMBER   OVER  PARTITION BY id ORDER BY rev DESC  rank           FROM YourTable  a  WHERE a rank   1    Added in SQL standard ANSI ISO Standard SQL 2003  and later extended with ANSI ISO Standard SQL 2008  window  or windowing  functions are available with all major vendors now  There are more types of rank functions available to deal with a tie issue  RANK  DENSE RANK  PERSENT RANK

User · Answer

Since this is most popular question with regard to this problem  I ll re-post another answer to it here as well   It looks like there is simpler way to do this  but only in MySQL    select   from  select   from mytable order by id  rev desc   x group by id   Please credit answer of user Bohemian in this question for providing such a concise and elegant answer to this problem      Edit  though this solution works for many people it may not be stable in the long run  since MySQL doesn t guarantee that GROUP BY statement will return meaningful values for columns not in GROUP BY list  So use this solution at your own risk

User · Answer

Here s another solution to retrieving the records only with a field that has the maximum value for that field  This works for SQL400 which is the platform I work on  In this example  the records with the maximum value in field FIELD5 will be retrieved by the following SQL statement   SELECT A KEYFIELD1  A KEYFIELD2  A FIELD3  A FIELD4  A FIELD5   FROM MYFILE A  WHERE RRN A  IN     SELECT RRN B         FROM MYFILE B      WHERE B KEYFIELD1   A KEYFIELD1 AND B KEYFIELD2   A KEYFIELD2      ORDER BY B FIELD5 DESC      FETCH FIRST ROW ONLY

User · Answer

How about this   SELECT all fields     FROM  SELECT id  MAX rev  FROM yourtable GROUP BY id  AS max recs   LEFT OUTER JOIN yourtable AS all fields  ON max recs id   all fields id

User · Answer

SELECT   FROM Employee where Employee Salary in  select max salary  from Employee group by Employe id  ORDER BY Employee Salary

User · Answer

If you have many fields in select statement and you want latest  value for all of those fields through optimized code   select   from  select   from table name order by id rev desc  temp group by id

User · Answer

Sorted the rev field in reverse order and then grouped by id which gave the first row of each grouping which is the one with the highest rev value   SELECT   FROM  SELECT   FROM table1 ORDER BY id  rev DESC  X GROUP BY X id    Tested in http   sqlfiddle com  with the following data  CREATE TABLE table1       id  int   rev  int   content  varchar 11     INSERT INTO table1       id    rev    content   VALUES      1  1   One-One         1  2   One-Two         2  1   Two-One         2  2   Two-Two         3  2   Three-Two         3  1   Three-One         3  3   Three-Three       This gave the following result in MySql 5 5 and 5 6   id  rev content 1   2   One-Two 2   2   Two-Two 3   3   Three-Two

User · Answer

I can t vouch for the performance  but here s a trick inspired by the limitations of Microsoft Excel   It has some good features  GOOD STUFF   It should force return of only one  max record  even if there is a tie  sometimes useful  It doesn t require a join   APPROACH  It is a little bit ugly and requires that you know something about the range of valid values of the rev column   Let us assume that we know the rev column is a number between 0 00 and 999 including decimals but that there will only ever be two digits to the right of the decimal point  e g  34 17 would be a valid value    The gist of the thing is that you create a single synthetic column by string concatenating packing the primary comparison field along with the data you want   In this way  you can force SQL s MAX   aggregate function to return all of the data  because it has been packed into a single column    Then you have to unpack the data   Here s how it looks with the above example  written in SQL  SELECT id          CAST SUBSTRING max packed col  FROM 2 FOR 6  AS float  as max rev         SUBSTRING max packed col  FROM 11  AS content for max rev  FROM   SELECT id          CAST 1000   rev    001 as CHAR      ---     CAST content AS char  AS packed col        FROM yourtable          GROUP BY id   The packing begins by forcing the rev column to be a number of known character length regardless of the value of rev so that for example   3 2    becomes    1003 201 57     becomes    1057 001 923 88 becomes    1923 881   If you do it right  string comparison of two numbers should yield the same  max  as numeric comparison of the two numbers and it s easy to convert back to the original number using the substring function  which is available in one form or another pretty much everywhere

[mysql] SQL select only rows with max value on a column

Examples related to mysql

Examples related to sql

Examples related to aggregate-functions

Examples related to greatest-n-per-group

Examples related to groupwise-maximum