Skip to Content
Author's profile photo Mohd Imran

RANK in BODS using gen_row_num_by_group() function


I am trying to explain Ranking in BODS. It plays very important role when your Business Requirement asks you to get most recent value within a group of values. We have a function called gen_row_num_by_group function() in BODS. With the help of this function you can generate RANK and then get Recent or Last value.

Go through the screens, it will tell you every step that you have to follow.

For Example:  Let’s take a scenario a customer has ordered few products on different dates. Now your business requirement wants to pick most recent date from Order date.

Now what you need to do is put order date in order by. That could be Ascending order.


Now Add a column you can name it as RANK and then map oreder_no with gen_row_num_by_group() function. It looks like below screen.


Now you run the job, you will get result like below table.


Now create another DF and make RANK TABLE as source. Map Oreder_date with max() function to pick most recent one. You would need group by Clause.


You have successfully generated RANK and picked up most recent date. Your result would look like below tables.




Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Dirk Venken
      Dirk Venken

      How is this different from the 1st solution in Efficient extraction of most recent data from a history table?

      Author's profile photo Prasad D
      Prasad D

      Dirk Venken, The above mentioned link is not working.


      Please check

      Author's profile photo Dirk Venken
      Dirk Venken

      The document was moved to wiki: How to extract most recent data from a history table