Grouped Limit in Postgresql: Show the First N Rows For Each Group

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;

Get different LIMIT on each group on postgresql rank

Create a table to contain the section limits, then join. The big advantage being that as new sections are required or limits change maintenance is reduced to a single table update and comes at very little cost. See example.

select s.section_id, s.name  
  from (select section_id, name 
             , row_number() over (partition by section_id order by name) rn
          from sections
       )  s 
  left join section_limits sl on (sl.section_id = s.section_id) 
where 
  s.rn <= coalesce(sl.limit_to,2);

PostgreSQL - Show the last N rows for each sorted group

If you want the data in a particular order, then you need to have an order by. SQL tables and result sets represent unordered sets. The only exception is when the outermost query has an order by.

So:

order by created_at desc

PostgreSQL limit by group, only show first 2 store options

Use the row_number() window function to accomplish this.

Query #1

    with first_two as (
      select *, 
             row_number() over (partition by store_name
                                    order by id) as rn
        from store_product
       where store_name <> 3
         and prod_name = 'A'
    )
    select id, store_name, prod_name
      from first_two
     where rn <= 2;

| id  | store_name | prod_name |
| --- | ---------- | --------- |
| 1   | 1          | A         |
| 4   | 1          | A         |
| 6   | 2          | A         |
| 8   | 2          | A         |

View on DB Fiddle

Select first `n` rows of a grouped query

The row_number function is exactly what you're looking for:

SELECT * 
FROM   (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY entry_time DESC) AS rn
        FROM   user_gps_location
        WHERE  entry_time > '2020-09-01') t
WHERE  rn <= 5

Select first row in each GROUP BY group?

On databases that support CTE and windowing functions:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rank
      FROM PURCHASES p)
 SELECT *
   FROM summary
 WHERE rank = 1

Supported by any database:

But you need to add logic to break ties:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total

---

Related Topics