Get the row(s) which have the max value in groups using groupby
In [1]: df
Out[1]:
Sp Mt Value count
0 MM1 S1 a 3
1 MM1 S1 n 2
2 MM1 S3 cb 5
3 MM2 S3 mk 8
4 MM2 S4 bg 10
5 MM2 S4 dgd 1
6 MM4 S2 rd 2
7 MM4 S2 cb 2
8 MM4 S2 uyi 7
In [2]: df.groupby(['Mt'], sort=False)['count'].max()
Out[2]:
Mt
S1 3
S3 8
S4 10
S2 7
Name: count
To get the indices of the original DF you can do:
In [3]: idx = df.groupby(['Mt'])['count'].transform(max) == df['count']
In [4]: df[idx]
Out[4]:
Sp Mt Value count
0 MM1 S1 a 3
3 MM2 S3 mk 8
4 MM2 S4 bg 10
8 MM4 S2 uyi 7
Note that if you have multiple max values per group, all will be returned.
Update
On a hail mary chance that this is what the OP is requesting:
In [5]: df['count_max'] = df.groupby(['Mt'])['count'].transform(max)
In [6]: df
Out[6]:
Sp Mt Value count count_max
0 MM1 S1 a 3 3
1 MM1 S1 n 2 3
2 MM1 S3 cb 5 8
3 MM2 S3 mk 8 8
4 MM2 S4 bg 10 10
5 MM2 S4 dgd 1 10
6 MM4 S2 rd 2 7
7 MM4 S2 cb 2 7
8 MM4 S2 uyi 7 7
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).
Return the row with max value for each group
A perfect use case for DISTINCT ON
:
SELECT DISTINCT ON (realm, race) *
FROM tbl
ORDER BY realm, race, total DESC;
db<>fiddle here
Notably, the query has no GROUP BY
at all.
Assuming total is NOT NULL
, else append NULLS LAST
.
In case of a tie, the winner is arbitrary unless you add more ORDER BY
items to break the tie.
Detailed explanation:
- Select first row in each GROUP BY group?
Select the row with the maximum value in each group based on multiple columns in R dplyr
We may get rowwise max of the 'count' columns with pmax
, grouped by 'col1', filter
the rows where the max
value of 'Max' column is.
library(dplyr)
df1 %>%
mutate(Max = pmax(count_col1, count_col2) ) %>%
group_by(col1) %>%
filter(Max == max(Max)) %>%
ungroup %>%
select(-Max)
-output
# A tibble: 3 × 4
col1 col2 count_col1 count_col2
<chr> <chr> <dbl> <dbl>
1 apple aple 1 4
2 banana banan 4 1
3 banana bananb 4 1
We may also use slice_max
library(purrr)
df1 %>%
group_by(col1) %>%
slice_max(invoke(pmax, across(starts_with("count")))) %>%
ungroup
# A tibble: 3 × 4
col1 col2 count_col1 count_col2
<chr> <chr> <dbl> <dbl>
1 apple aple 1 4
2 banana banan 4 1
3 banana bananb 4 1
Select the row with the maximum value in each group
Here's a data.table
solution:
require(data.table) ## 1.9.2
group <- as.data.table(group)
If you want to keep all the entries corresponding to max values of pt
within each group:
group[group[, .I[pt == max(pt)], by=Subject]$V1]
# Subject pt Event
# 1: 1 5 2
# 2: 2 17 2
# 3: 3 5 2
If you'd like just the first max value of pt
:
group[group[, .I[which.max(pt)], by=Subject]$V1]
# Subject pt Event
# 1: 1 5 2
# 2: 2 17 2
# 3: 3 5 2
In this case, it doesn't make a difference, as there aren't multiple maximum values within any group in your data.
Select rows with highest value from groupby
What if you have two maxes for a name with different types:
print(df)
name type count
0 robert x 123
1 robert y 456
2 robert z 5123
3 robert a 5123
4 charlie x 442123
5 charlie y 0
6 charlie z 42
Use boolean indexing:
df[df['count'] == df.groupby('name')['count'].transform('max')]
Output:
name type count
2 robert z 5123
3 robert a 5123
4 charlie x 442123
Return row with the max value of one column per group
This is also possible without subquery:
SELECT DISTINCT
id
,max(round) OVER (PARTITION BY id) AS round
,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score
FROM SCORES
WHERE id IN (1,2,3)
ORDER BY id;
Returns exactly what you asked for.
The crucial point is that DISTINCT
is applied after window functions.
SQL Fiddle.
Maybe faster because it uses the same window twice:
SELECT DISTINCT
id
,first_value(round) OVER (PARTITION BY id ORDER BY round DESC) AS round
,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score
FROM SCORES
WHERE id IN (1,2,3)
ORDER BY id;
Otherwise doing the same.
get rows with largest value in grouping
Use DataFrameGroupBy.idxmax
if need select only one max value:
df = df.loc[df.groupby('id')['value'].idxmax()]
print (df)
id other_value value
2 1 b 5
5 2 d 6
7 3 f 4
10 4 e 7
If multiple max values and want seelct all rows by max
values:
df = pd.DataFrame({'id' : [1, 1, 1, 2, 2, 2, 3, 3, 4, 4, 4, 4],
'other_value' : ['a', 'e', 'b', 'b', 'a', 'd', 'b', 'f' ,'a' ,'c', 'e', 'f'],
'value' : [1, 3, 5, 2, 5, 6, 2, 4, 6, 1, 7, 7]
})
print (df)
id other_value value
0 1 a 1
1 1 e 3
2 1 b 5
3 2 b 2
4 2 a 5
5 2 d 6
6 3 b 2
7 3 f 4
8 4 a 6
9 4 c 1
10 4 e 7
11 4 f 7
df = df[df.groupby('id')['value'].transform('max') == df['value']]
print (df)
id other_value value
2 1 b 5
5 2 d 6
7 3 f 4
10 4 e 7
11 4 f 7
row with max value per group - SQLite
Join against that result to get the complete table records
SELECT t1.*
FROM your_table t1
JOIN
(
SELECT name, Max(population) as max_population
FROM your_table
WHERE name IN ('a', 'b', 'c')
GROUP BY name
) t2 ON t1.name = t2.name
and t1.population = t2.max_population
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.
Related Topics
Error: Tcp Provider: Error Code 0X2746. During the SQL Setup in Linux Through Terminal
How to Use Group by to Concatenate Strings in SQL Server
Identity Increment Is Jumping in SQL Server Database
How to Delete Duplicate Rows in SQL Server
Querying Spark SQL Dataframe With Complex Types
Computed/Calculated/Virtual/Derived Columns in Postgresql
MySQL Error:: 'Access Denied For User 'Root'@'Localhost'
How to Get Column Names from a Table in SQL Server
Strange Duplicate Behavior from Group_Concat of Two Left Joins of Group_Bys
Maximum Size For a SQL Server Query? in Clause? Is There a Better Approach
Bash Script to Insert Values in MySQL
SQL to Linq With Multiple Join, Count and Left Join
Split Function Equivalent in T-Sql
Managing and Debugging SQL Queries in Ms Access
MySQL: @Variable Vs. Variable. What's the Difference
Dynamic Alternative to Pivot With Case and Group By