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

    Calculate record rank within a subset in a specific sort

    wladdy

      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
          philmodjunk

          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.