I'm using mysql 8.0 and the table I have has a lot of rows so the solutions from this link take too long to run.
Table example:
| ID | Name | Value | Category |
|---|---|---|---|
| 1 | a | 5 | alpha |
| 2 | b | 7 | beta |
| 3 | c | 8 | alpha |
| 4 | d | 10 | beta |
- I would like to group it by category and then select the max value in each category
- If the max values collide, I'd like to get the smallest ID (it will always be unique in my case)
Output table:
| ID | Name | Value | Category |
|---|---|---|---|
| 3 | c | 8 | alpha |
| 4 | d | 10 | beta |
The ID and Category column are indexed and I need this query to be fast.
CodePudding user response：
- I would like to group it by
categoryand then select themax valuein each category- If the max values collide, I'd like to get the
smallest ID(it will always be unique in my case)
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Value DESC, Id ASC) rn
FROM src_table
)
SELECT *
FROM cte
WHERE rn = 1