Get non-aggregated column values without joins MySQL

Question

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

1. I would like to group it by category and then select the max value in each category
2. 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.

Answer 1

1. I would like to group it by category and then select the max value in each category
2. 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