1 Reply Latest reply on Jun 1, 2011 8:43 AM by philmodjunk

Calculate record rank within a subset in a specific sort

Title

Calculate record rank within a subset in a specific sort

Post

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.

• 1. Re: Calculate record rank within a subset in a specific sort

Determining "rank" can be sticky in some situations if duplicate values can occur in the value(s) used to rank the records. Since this is not the case here a summary field can be used to display the correct rank when the values are sorted in this fashion.

Define a summary field, sRank and select the Count of, Page_ID options. Then select "running count", "Restart Summary for each sorted group" and select the table where you'ved defined these fields in the drop down and Page_ID as the "group by" field.

When you sort your records as you've specified here, sRank will display the proverb_Rank you've requested.