AnsweredAssumed Answered

Calculate record rank within a subset in a specific sort

Question asked by wladdy on May 31, 2011
Latest reply on Jun 1, 2011 by philmodjunk


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.