Sample Header Ad - 728x90

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