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

by herminia_bruen , in category: MySQL , 18 days 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 , 15 days 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.