PostgresSQL- find 3 lowest values based on another column

Question

I am challenging this issue. I have a table that calls: results. There are 3 columns and it looks like below (id column is int, city is varchar, score is int):

id   name   score
1    x      5
2    x      9
3    x      10
5    x      2
85   y      20
2    y      1
9    z      98
2    z      6
7    z      93
10   z      9

I have to find 3 lowest values for each name so the output should be like this:

id   name   score
1    x      5
2    x      9
5    x      2
85   y      20
2    y      1
2    z      6
7    z      93
10   z      9

So I tried to write sql query like this:

SELECT id, name, score
FROM results
GROUP BY name
ORDER BY score DESC
LIMIT 3

But it doesn't work (output is wrong). Do you have any idea how I could solve this?

Answer 1

Assuming you don't care about ties, you may use ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY score) rn
    FROM results
)

SELECT id, name, score
FROM cte
WHERE rn <= 3
ORDER BY name;