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.
Advertisement
Answer
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:
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: