How does one get the total rows for a partition in postgresql-CodePudding

I'm using a windows function to help me pagination through a list of records in the database.

For example I have a list of dogs and they all have a breed associated with them. I want to show 10 dogs from each breed to my users.

So that would be

select * from dogs 
join (
SELECT id, row_number() OVER (PARTITION BY breed) as row_number FROM dogs
) rn on dogs.id = rn.id
where (row_number between 1 and 10)

That will give me ~ten dogs from each breed..

What I need though is a count. Is there a way to get the count of the partitions. I want to know how many Staffies I have waiting for adoption.

I do notice that there's a percentage and all the docs I find seem to indicate theres something called total rows. But I don't see it.

CodePudding user response：

Just run the window aggregate function count() over the same partition (without adding ORDER BY!) to get the total count for the partition:

SELECT *
FROM  (
   SELECT *
        , row_number() OVER (PARTITION BY breed ORDER BY id) AS rn
        , count() OVER (PARTITION BY breed) AS breed_count          -- !
   FROM   dogs
   ) sub
WHERE  rn < 11;

Also removed the unnecessary join and simplified.
See:

Run a query with a LIMIT/OFFSET and also get the total number of rows

And I added ORDER BY to the frame definition of row_number() to get a deterministic result. Without, Postgres is free to return any 10 arbitrary rows. Any write to the table (or VACUUM, etc.) can and will change the result without ORDER BY.

Aside, pagination with LIMIT / OFFSET does not scale well. Consider:

Optimize query with OFFSET on large table