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.
MySQL - How to select rows with max value of a field
If you want to get ties, then you can do something like this:
select s.*
from scores s
where s.score = (select max(s2.score) from scores s2 where s2.level = s.level);
You could get one row per level by aggregating this:
select s.level, s.score, group_concat(s.user_id)
from scores s
where s.score = (select max(s2.score) from scores s2 where s2.level = s.level)
group by s.level, s.score;
This combines the users (if there is more than one) into a single field.
How to select a maximum value row in mysql table
Question 1 : What I made mistake here and why this MAX function is not return the relevant row information?
You need to read up on the group by
clause.
MySQL is being a lot more permissive than it should, introducing confusion in the process. Basically, any column without an aggregate should be included in the group by
clause. But MySQL syntactic sugar allows to "forget" columns. When you do, MySQL spits out an arbitrary value from the set that it's grouping by. In your case, the first row in the set is bob
, so it returns that.
Question 2: Which one is good to use, to increase performance MAX function or ORDER BY clause?
Your first statement (using max()
without a group by
) is simply incorrect.
If you want one of the oldest users, order by age desc limit 1
is the correct way to proceed.
If you want all of the oldest users, you need a subselect:
SELECT p.* FROM people p WHERE p.age = (select max(subp.age) from people subp);
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 a row with MAX(value) from a derived table where the values are all calculated sums?
Here's a solution that should work on any version of MySQL 5.x, using no ORDER BY, LIMIT, window functions, views, or CTEs.
SELECT a.stagename, a.total_salary
FROM (
SELECT c.*, sum(p.dailySalary) as total_salary
from contender as c
left join participant as p
on (p.contender = c.idContender)
group by c.idContender ) AS a
LEFT OUTER JOIN (
SELECT c.*, sum(p.dailySalary) as total_salary
from contender as c
left join participant as p
on (p.contender = c.idContender)
group by c.idContender ) AS b
ON a.total_salary < b.total_salary
WHERE b.total_salary IS NULL;
Tested on MySQL 5.7.27.
Output:
+-------------+--------------+
| stagename | total_salary |
+-------------+--------------+
| Bobbleheads | 11840 |
+-------------+--------------+
Select all rows where maximum value on a one column from two tables with union
As fix_id
is unique in both tables, the answer with CASE
statements (https://stackoverflow.com/a/65609931/53341) is likely the fastest (so, I've upvoted that)...
- Join once
- Compare rates, on each row
- Pick which table to read from, on each row
For large numbers of columns, however, it's unwieldy to type all the CASE
statements. So, here is a shorter version, though it probably takes twice as long to run...
SELECT t1.*
FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.fix_id = t2.fix_id
WHERE t1.rate >= t2.rate
UNION ALL
SELECT t2.*
FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.fix_id = t2.fix_id
WHERE t1.rate < t2.rate
MySQL - Is there a way I can get the other columns of a row which got the MAX value
You can use a subquery to list all the records with the maximum value:
SELECT 'record_id', download_count as 'max_dl' FROM 'downloads' WHERE download_count = (SELECT MAX(download_count) FROM 'downloads')
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 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
Related Topics
Using Object_Id() Function with #Tables
Login Failed. the Login Is from an Untrusted Domain and Cannot Be Used with Windows Authentication
Postgres Query of an Array Using Like
Why Doesn't Oracle Raise "Ora-00918: Column Ambiguously Defined" for This Query
Performance of String Comparison VS Int Join in SQL
Sql: Syntax Error with Intersect
Oracle 12C - Select String After Last Occurrence of a Character
SQL - Safely Downcast Bigint to Int
SQL Varchar(Max) VS Varchar(Fix)
Ssrs Remove Column from Report
Sql:Casting a String to Ids with in Clause
Natural Sort Supporting Big Numbers