How to get the top row for each group in a column in mysql?

by herminia_bruen , in category: MySQL , a year ago

How to get the top row for each group in a column in mysql?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

by darrion.kuhn , a year ago

@herminia_bruen 

To get the top row for each group in a column in MySQL, you can use the following query with a combination of subqueries:

1
2
3
4
SELECT t1.*
FROM your_table t1
LEFT JOIN your_table t2 ON t1.group_column = t2.group_column AND t1.column > t2.column
WHERE t2.column IS NULL;


Here's how the query works:

  1. The table your_table is self-joined using the LEFT JOIN clause, where each row from t1 is paired with the rows in t2 having the same group value, but with a lesser value in the column you want to compare (column in this case).
  2. The WHERE clause filters out the rows from t1 which have no corresponding row in t2. This allows only the top row(s) for each group to be selected.


Replace your_table with the actual name of your table, and group_column and column with the respective column names in your table.

Related Threads:

How to get maximum column values across a row in teradata sql?
How can find sum of a column after group by result in mysql?
How to get the last data of group by in mysql?
How to group time column into 5 second intervals and count rows using presto?
How to get index of no zero column in mysql?
How to get "not null column name" in mysql database?