Search code examples
mysqlsqlgreatest-n-per-group

How to get lowest value from posts with the same ID

My sample table:

ID | Post_id | Score
1  | 1       | 33
2  | 1       | 43
3  | 1       | 27
4  | 1       | 66

I want to get rows with the lowest value (Score). In this case it is:

ID | Post_id | Score
3  | 1       | 27

My query:

SELECT * FROM table WHERE post_id = '1' GROUP BY post_id ORDER BY Score ASC

But that doesn't work because it returns me: Score: 33

How to fix it? What if I have thousands of rows and want post_id to be unique for the lowest values?

Solution

  • You must use subquery selecting min values for each post_id.

    SELECT a.* FROM records a
JOIN 
( SELECT post_id, MIN(score) as min_score
  FROM records  GROUP BY post_id
) b
ON a.score=b.min_score;

    Output

    | id  | post_id | score |
| --- | ------- | ----- |
| 3   | 1       | 27    |
| 5   | 2       | 20    |

    View on DB Fiddle