SQL select only rows with max value on a column
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 Stack Overflow 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 aLEFT JOIN
, remember?). Then, we filter the joined result, showing only the rows where the right side isNULL
.
So you end up with:
SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
ON a.id = b.id AND a.rev < 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.
Get records with max value for each group of grouped SQL results
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 "non aggregated columns are not listed in the group by clause" 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).
How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten
table on BOTH fields:
SELECT tt.*
FROM topten tt
INNER JOIN
(SELECT home, MAX(datetime) AS MaxDateTime
FROM topten
GROUP BY home) groupedtt
ON tt.home = groupedtt.home
AND tt.datetime = groupedtt.MaxDateTime
How to get MAX() value and Record Primary Key where the value occurs in a Database?
So I found this solution. This would give the Primary Key
or the record ID
. It also allows for the possibility that there is more than one record with the MAX()
or MIN()
value.
SELECT ID, Name, Hours AS [Max] FROM Professions
WHERE Hours = (SELECT MAX(Hours) FROM Professions)
ORDER BY ID ASC
Here is proof using another database, though the same concept applies.
I actually wrote this code while working on a completely different problem.
how to get a Row with Max value of a column?
You can use a correlated subquery:
select t.*
from mytable t
where t.srno = (select max(srno) from mytable t1 where t1.p_id = t.p_id)
With an index on (p_id, srno)
, this should be an efficient solution.
Anoter common solution is to use row_number()
:
select pid, name, srno, rate
from (
select t.*, row_number() over(partition by p_id order by srno desc) rn
from mytable t
) t
where rn = 1
How to get all records having maximum value in a SQL Server table
In SQL Server, you can use top (1) with ties
:
select top (1) with ties birdType, count(1) AS birdCount
from migratorybirds
group by birdType
order by count(1) desc;
Find records with MAX value in each
Check this updated Fiddle of yours: http://sqlfiddle.com/#!2/0ca23/4
SELECT temp.*
FROM temp
JOIN
(SELECT `Country`, `City`, `Resource`, MAX(`Volume`) AS MaxVol
FROM `temp`
GROUP BY `Country`, `City`, `Resource`) t
ON temp.country = t.country
AND temp.city = t.city
AND temp.resource = t.resource
AND temp.volume = t.MaxVol
This query is basically making a INNER JOIN of your main table with the subquery which gets the max(volume) records for each country, city, and resource. The subquery results are aliased as table t
.
Related Topics
Selecting Most Recent and Specific Version in Each Group of Records, for Multiple Groups
SQL Join Table Naming Convention
How to Connect to SQL Express "Error: 26-Error Locating Server/Instance Specified)
Count Rows Per Hour in SQL Server with Full Date-Time Value as Result
How to Find the Worst Performing Queries in SQL Server 2008
Limit Results from Joined Table to One Row
How to See Generated SQL from a Linq Query
SQL Selecting Rows Where One Column's Value Is Common Across Another Criteria Column
How to Use Non-Aggregate Columns with Group By
How to Calculate the Last Day of the Month in SQL
How to Store Longitude & Latitude as a Geography in SQL Server 2014
SQL How to Convert Row with Date Range to Many Rows with Each Date
What Happens When You Hit the SQL Server Express 4Gb/10Gb Limit
SQL Query for Finding Rows with Special Characters Only