Postgres select (group by type)

Question

i have table like:

ID     NAME      TYPE  RATING
1      name1      1      98
2      name2      1      17
3      name3      2      77
4      name4      2      53
5      name5      2      23
6      name6      4      64
7      name7      3      78
8      name8      3      56
9      name9      3      22
10     name10     4      56
11     name11     4      99
.      ...        .      ..

how can i select from this table, and for example only (2,3...etc, N) rows of each 'type' with highest rating?

Result example(for 2 rows):

ID     NAME      TYPE  RATING
1      name1      1      98
2      name2      1      17
3      name3      2      77
4      name4      2      53
7      name7      3      78
8      name8      3      56
6      name6      4      64
11     name11     4      99
.      ...        .      ..

score 3 · Answer 1 · answered Jan 16 '16 at 08:22

You could use the row_number window function to assign a ranking to each row, per type:

SELECT id, name, type, rating
FROM   (SELECT id, name, type, rating,
               ROW_NUMBER() OVER (PARTITION BY type ORDER BY rating DESC) AS rn
        FROM   myable)
WHERE  rn <= 3; -- Or any other number of rows per type you wish

Postgres select (group by type)

1 Answers1