Search code examples
sqlpostgresqlfetchlimitoffset

Why LIMIT и OFFSET (OFFSET ... ROWS FETCH FIRST ... ROW only) functions in PostgreSQL are not working?

I am trying to use LIMIT and OFFSET functions or OFFSET ... ROWS FETCH FIRST ... ROW only. PostgreSQL gives me the wrong number of rows in result.

select user_id, max(order_ts) as lastorder
from production.orders
group by user_id 
order by lastorder desc, user_id desc
OFFSET 10 ROWS 
FETCH FIRST 20 ROW only

or

select user_id, max(order_ts) as lastorder
from production.orders
group by user_id 
order by lastorder desc, user_id desc
OFFSET 10 
limit 20

Still gives me 20 rows (should be 10: from 10th row to 20th - is 10).

How is this? Any help, please?

Solution

  • LIMIT 20 tells server to return not more than 20 records. FETCH FIRST 20 ONLY is absolutely the same. The query might return 20 rows or less depending on the data and query conditions. If you are trying to get rows from 11th to 20th then you need to specify LIMIT 10 OFFSET 10.

    See the paragraph LIMIT Clause in the documentation for details: https://www.postgresql.org/docs/15/sql-select.html#SQL-LIMIT