ROW_NUMBER() AND RANK() from SQL in Alteryx

DSNY2 just finished week 3 by taking the Alteryx Designer Core Certification Exam, so it’s a good time to review one of the challenges I enjoyed.

Alteryx Weekly Challenge #122: Think Like a CSE...Ranking Data

The challenge was to give the rating column a rank, as it would be ranked using RANK() in SQL, and I inadvertently managed to do ROW_NUMBER() (and PARTITION BY them) as well.

My thought process was to find a pattern between Rating and the Rank.

If Rating is the same as the one above it, then it shares the same rank. If it isn’t the same, it is just the row number.


Thus, my goals were to:

  1. Create something for the Row Number - which would be ROW_NUMBER() in SQL.
  2. Write a formula that will take the Rank from the row above if the Rating is the same, or else return the new Row Number field - which would be RANK().

Starting off, the challenge provides the input and output tables.

Input:

Output:

1a. Create a new field that imitates ROW_NUMBER(). There’s at least two ways to do it. The way I originally created it was using the Multi-Row Formula tool under Preparation.

The expression is incrementing the previous rows’ value by 1 for the current row.

While slightly more complicated, with the Multi-Row Formula, you are able to group by a value - i.e. partitioning by - see the optional options in the blue box.

1b. Another way, perhaps simpler, was pointed out to me: through the Record ID Tool under Preparation.

All you have to do is input your starting value, and it will increment it for every record you have (though there is no grouping by with Record ID).


2. To create the ranking, I used another Multi-Row Formula Tool to conduct the logic I laid out earlier: take the Rank from the row above if the Rating is the same, or else return the new Row Number field.

The result - a ranking that can be grouped by, and will rank new data that comes in - just sort your data descending order first.

My overall workflow:

Author:
Bianca Ng
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2025 The Information Lab