1

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Tom Harisond
  • 57
  • 1
  • 10

5 Answers5

3

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

Jsowa
  • 9,104
  • 5
  • 56
  • 60
2

For a single id, just remove the group by and use limit:

SELECT *
FROM table
WHERE post_id = 1
ORDER BY Score ASC
LIMIT 1;

I assume that post_id is a number. Compare numbers to numbers, not to strings.

EDIT:

If you want this per post_id, then just use a correlated subquery:

select t.*
from t
where t.score = (select min(t2.score) from t t2 where t2.post_id = t.post_id);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • What if I have thousands of rows and want post_id to be unique for the lowest values? – Tom Harisond Oct 01 '20 at 13:55
  • 1
    @TomHarisond . . . Then you would have a different question. You should be clear in the questions that you ask. The purpose of this site is not "guess my question". The purpose is to answer well-constructed questions. The question you have here is clearly filtering by `post_id`. – Gordon Linoff Oct 01 '20 at 14:04
0

If you may have multiple rows with the lowest score, you can do it with a sub-query :

SELECT *
FROM test
WHERE post_id = 1
AND score = (
  SELECT MIN(score)
  FROM test 
  WHERE post_id = 1
)

Fiddle : https://www.db-fiddle.com/f/3ppntnA77HFpKRU82h32Gv/1

Obzi
  • 2,384
  • 1
  • 9
  • 22
0

The below should do the trick:

Select
    id, 
    score, 
    Post_id,
    min(score)
from 
    table
where 
    score = min(score);
Grzegorz Skibinski
  • 12,624
  • 2
  • 11
  • 34
janik
  • 11
  • 1
0

IF you're using MySQL v8.0, you can use the ROW_NUMBER() function to order the result. That way you can choose the row with the lower score and return everything from it:

select
    sq.id, sq.post_id, sq.score
from
    (select id, post_id, score
        , row_number() over (partition by post_id order by score) RowNo 
    from test) sq
where sq.RowNo = 1

Here is a Fiddle to test the code: https://www.db-fiddle.com/#&togetherjs=8dHSCs50Iq

I also included another post_id beside your sample data, to demonstrate how it reacts to multiple post_id's

lakta
  • 278
  • 1
  • 9