1

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
baash05
  • 4,394
  • 11
  • 59
  • 97
  • No. There is not a percentage of docs that *indicate there's something called total rows*, because it does not exist. What you find is that there are a percentage of docs that **generate** a column aliased as total_rows. – Belayer Sep 28 '21 at 01:01

1 Answers1

3

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • table will never have more than 8k records.. But yeah What got me was that I'd put the order by on the count too. It broke a pile of things – baash05 Sep 29 '21 at 10:30