I have a sample table named assets which looks like this:

There can be any number of assets in a specific block. I need a minimum of 100 rows from the table, and containing all the data from the block_no. Like, if there are 95 rows to block_no 2 and around 20 on block_no 3, I need all 20 of block_no 3 as if I am fetching data in packets based on block_no.
Is this possible and feasible?

CodePudding user response：

Postgres 13 or later

There is a dead simple solution using WITH TIES in Postgres 13 or later:

SELECT *
FROM   assets
WHERE  block_no >= 2  -- your starting block
ORDER  BY block_no
FETCH  FIRST 100 ROWS WITH TIES;

This will return at least 100 rows (if enough qualify), plus all peers of the 100th row.

If your table isn't trivially small, an index on (block_no) is essential for performance.

See:

• Greater than or equal to ALL() and equal to MAX() speed

Older versions

Use the window function rank() in a subquery:

SELECT (a).*
FROM  (
   SELECT a, rank() OVER (ORDER BY block_no) AS rnk
   FROM   assets a
   ) sub
WHERE  rnk <= 100;

Same result.

I use a little trick with the row type to strip the added rnk from the result. That's an optional addition.

See:

• PostgreSQL equivalent for TOP n WITH TIES: LIMIT "with ties"?