Home SQL select only rows with max value on a column
Reply: 0

SQL select only rows with max value on a column

user10821
1#
user10821 Published in September 19, 2018, 6:44 pm

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.

share|improve this question
  • Do you need the corresponding content field for the row? – Mark Byers Oct 12 '11 at 19:45
  • Yes, and that would pose no problem, I have cut out many columns which I'd be adding back. – Majid Fouladpour Oct 12 '11 at 19:48
  • 1
    @MarkByers I have edited my answer to comply with OP needs. Since I was at it, I decided to write a more comprehensive answer on the greatest-n-per-group topic. – Adrian Carneiro Oct 12 '11 at 20:57
  • This is common greatest-n-per-group problem, which has well tested and optimized solutions. I prefer the left join solution by Bill Karwin (the original post). Note that bunch of solutions to this common problem can surprisingly be found in the one of most official sources, MySQL manual! See Examples of Common Queries :: The Rows Holding the Group-wise Maximum of a Certain Column. – TMS Apr 28 '14 at 11:50
  • 1
    duplicate of Retrieving the last record in each group – TMS Jul 8 '14 at 18:39

29 Answers 29

active oldest votes
up vote 1449 down vote accepted
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.479431 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO