Skip to content
Advertisement

Get non-aggregated column values without joins MySQL

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.

Advertisement

Answer

  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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement