Get total number of rows when offset is greater than max
2
votes
1
answer
3616
views
I have a table
users
that has, say, 42 records. To get the total rows and some other details, I use a window function:
select count(*) over() as count, name, id from users
And since the consumer app is paginated, I can utilise limit
and offset
to get the desired data:
select count(*) over() as count, name, id from users limit 5 offset 20
This gives me a (sample) result:
count | name | id
-------+---------+----
42 | Dummy05 | 27
42 | Dummy06 | 30
42 | Dummy07 | 31
42 | Dummy08 | 32
42 | Dummy09 | 33
(5 rows)
Of course, this can be optimised as described in this answer .
However when the offset value overshoots, I get 0 rows which is correct.
select count(*) over() as count, name, id from users limit 5 offset 50
Is there a way that gives me the total count despite the offset returning 0 rows? Something on the similar lines:
count | name | id
-------+---------+----
42 | |
(0 rows)
Asked by Slartibartfast
(123 rep)
Jan 31, 2019, 01:26 PM
Last activity: Jan 31, 2019, 03:02 PM
Last activity: Jan 31, 2019, 03:02 PM