Update row according to result number in result set

Question

I am looking to do the following:

tblOne:
-page_id
-split



tblMany
-view_id
-page_id

I want to order tblOne by the number of related page_id in tblMany.

Then divide the number of rows in tblOne by 5 and update tblOne.split to a number between 1 - 5 into which split it falls... e.g if there are 50 rows... row 0 - 9 are split 1, 10 - 19 split 2...etc

I am sure I can do the 'count' part... but havn't a clue how I would update the 'split' row

Answer 1

This query will give you the distinct page id and their count ordered by page_id count in descendant order (so max count first) :

SELECT page_id, count(1) 
FROM tblMany 
GROUP BY 1 
ORDER BY 2 DESC

So for a tblMany like this:

view_id | page_id
--------+---------
  1         1
  2         1
  3         2
  4         2
  5         2
  6         3

You will get

 page_id | count(1)
 --------+---------
    2        3
    1        2
    3        1