Postgresql: Top N Entries Per Item in Same Table

PostgreSQL: top n entries per item in same table

SELECT *
FROM (
   SELECT uid,
          title, 
          amount, 
          maker, 
          widgets,
          rank() over (partition by maker order by amount desc) as rank
   FROM entry  
) t
WHERE rank <= 3

Grouped LIMIT in PostgreSQL: show the first N rows for each group?

New solution (PostgreSQL 8.4)

SELECT
  * 
FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY section_id ORDER BY name) AS r,
    t.*
  FROM
    xxx t) x
WHERE
  x.r <= 2;

Find the top 2 records for each key in a table

Using RANK() we can try:

WITH cte AS (
    SELECT *, RANK() OVER (PARTITION BY game_id ORDER BY score DESC) rnk
    FROM yourTable
)

SELECT game_id, player_id, score
FROM cte
WHERE rnk <= 2
ORDER BY game_id, score DESC;

Note that if there be the possibility of ties, then you might want to use DENSE_RANK instead of RANK. If ties are not a concern, then you could also use ROW_NUMBER instead of RANK.

How to get top value in Postgres when ties?

There is an SQL standard way to do this. It doesn't use the LIMIT keyword, but rather FETCH

FETCH FIRST 1 ROWS WITH TIES

But support for the WITH TIES part of the standard wasn't added to PostgreSQL until v13.

Update the top n rows of a table in Postgres

How about

update trace_terciles set tercile=2 where year in (select year from trace_terciles order by value desc limit 12 offset 12)

and similarly for the other 2 cases.

---

Related Topics