Total row count + select with limit

Question

my question is similar to this one: Need a row count after SELECT statement: what's the optimal SQL approach?

But I want to get the total of rows from a query, and then use limit to create pagination so I can't use the returned rows. From a simple query:

select * from res_groups

I got into this:

select a.*, (select count(1) from (select * from res_groups) e) total 
from (select * from res_groups) a limit 10 offset 10;

Or I could use the easy approach and make two queries:

select * from res_groups limit 10;
select count(*) from res_groups;

Will the first query be performatic? I'm afraid that the query from res_groups will be executed twice?

Is there any other way? ps: I'm using postgres, I know that mysql has FOUND_ROWS()

Chris Travers Chris Travers · Accepted Answer · 2013-12-12T01:56:09

What about:

WITH  a AS (select *, count(*) over (range unbounded preceding)
         FROM resgroups)
SELECT * from a order by foo limit 10 offset 10;

Now, I think you are actually better off to break this into two queries though because it looks like you are doing paging, effectively. If you select the count(*) first, and then decide how many pages you need (and maybe cache that result) then your subsequent partial queries can use an index, but in this case, every group of 10 will require a full sequential scan.

Total row count + select with limit

1 Answers