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

    Calculate record rank within a subset in a specific sort



      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.

        • 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.