Calculate record rank within a subset in a specific sort
A table has two number fields: 'Page_ID' and 'Proverb_ID'.
'Proverb_ID' are consective and unique integers from 1 to n.
'Page_ID' are consective but not unique integers from 1 to m.
When the table is sorted by 'Proverb_ID', 'Page_ID' for each record will either keep the value of the previous 'Page_ID' or increment it by 1.
We need to add a field 'Proverb_Rank' giving us the rank of a Proverb on its page, when the table is sorted by 'Proverb_ID'.
Our final table could look like:
Page_ID Proverb_ID Proverb_Rank
1 1 1
1 2 2
1 3 3
2 4 1
3 5 1
3 6 2
4 7 1
4 8 2
4 9 3
4 10 4
My solution was to populate 'Proverb_Rank' using a script. However, I wonder whether there is a way to make 'Proverb_Rank' a calculated field.
More generally, what are the best practices to find the rank of any given record among all records sharing a common foreign key and sorted by an numeric primary key ?
Many thanks. W.