Ranking Rows by Value: Top Salespeople, Best Products

You exported your Q1 sales data. You need to put a rank number next to each salesperson based on their total revenue. Employee #1, #2, #3. Sounds obvious, but as soon as two people have the same revenue, things get interesting. Are they both #3? Does the next person become #4 or #5?

SQL has three different ranking functions, and they each answer that question differently. ExploreMyData lets you pick the one that matches what you actually need.

The three ranking functions

Let's say five salespeople have revenue of $500, $400, $400, $300, $200. Here's how each function handles it:

• ROW_NUMBER: 1, 2, 3, 4, 5 - every row gets a unique number, ties are broken arbitrarily
• RANK: 1, 2, 2, 4, 5 - ties share a rank, then a gap (no rank 3)
• DENSE_RANK: 1, 2, 2, 3, 4 - ties share a rank, no gap

Which one to use? ROW_NUMBER when you need strictly unique positions (like assigning sequential IDs). RANK when ties should share a position and you want the gap to reflect how many people are ahead of you. DENSE_RANK when ties share a position and you just want consecutive numbers.

Most people want DENSE_RANK. It feels the most natural: "these two products are both #2, the next one is #3." RANK is better for competitive contexts: "you're in 4th place because three people scored higher than you, even though two of them tied."

Adding a rank column

Click the green + in the Pipeline panel and select Window Function from the Aggregate group. Pick DENSE_RANK as the function and watch what happens to the panel: the Column field disappears. ROW_NUMBER, RANK and DENSE_RANK count positions rather than read a value out of a column, so the app hides that field for all three.

The column you actually rank by is the Order by column. Set it to revenue. A direction switch appears next to it the moment you choose a column, offering Largest first and Smallest first. Leave it on Largest first, which is the default. That setting is what makes rank 1 the top earner. Last, give the output column a name; leave the field blank and you get dense_rank_result.

The generated SQL:

DENSE_RANK() OVER (ORDER BY "revenue" DESC) AS "dense_rank_result"

Flip the switch to Smallest first and the ORDER BY becomes ASC, which makes rank 1 the lowest revenue. Nothing errors. The column fills in, the numbers look plausible, and your leaderboard is upside down. That is the classic silent wrong answer with ranking, so check the direction before you trust the output.

Every row now carries a rank number, and the person with the highest revenue is rank 1. Click the new column's header to sort the grid view by it and you are looking at your leaderboard.

Ranking within groups

Ranking gets more useful when you do it within groups. Instead of one global leaderboard, you want the top salesperson in each region. Or the best-selling product in each category.

Set the Partition by field to your grouping column and name the output region_rank. If you partition by region, each region gets its own ranking starting from 1.

DENSE_RANK() OVER (PARTITION BY "region" ORDER BY "revenue" DESC) AS "region_rank"

The top earner in "East" is rank 1. The top earner in "West" is also rank 1. They each won their region. This is incredibly useful for finding the best performer in each group without having to filter the data multiple times.

Getting the top N per group

Once you have a rank column, filtering to the top 3 per group is straightforward. Add a Filter step where region_rank <= 3. Now you have a table showing only the top 3 salespeople in each region.

This is a pattern that comes up constantly in reporting: top 5 products per category, top 10 customers per segment, worst-performing stores per territory. The rank-then-filter approach handles all of them.

Ranking by multiple criteria

What if revenue is the primary ranking, but you want to break ties by number of deals? The Window Function panel cannot express that. Its Order by field is a single-select dropdown: one column, one direction, and no second field for a tiebreaker. Once you need two sort keys you have outgrown the panel.

The workaround is Add Column from the Columns group, where the expression box takes the whole window function as free text and you can list as many ORDER BY keys as you like:

DENSE_RANK() OVER (ORDER BY "revenue" DESC, "num_deals" DESC)

When two people have the same revenue, the one with more deals ranks higher. This is common in sales contexts where volume matters as a tiebreaker.

Percentile ranking

Sometimes an absolute rank is not the question. You want to know where a value falls relative to the whole distribution: "this product is in the 90th percentile for revenue."

PERCENT_RANK is not in the Window Function dropdown either, so this is another Add Column job. Name the new column percentile and paste this into the expression box:

ROUND(PERCENT_RANK() OVER (ORDER BY "revenue") * 100, 1)

This returns a number between 0 and 100. A percentile of 95 means this row's revenue is higher than 95% of all other rows. Useful for identifying outliers and segmenting data into tiers (top 10%, middle 50%, bottom 40%).

A note on pre-aggregated vs raw data

If your data is one row per transaction, aggregate before you rank. Ranking individual transactions rarely tells you anything. Use Group & Aggregate from the Aggregate group: set Group by to salesperson and add one aggregation row, function SUM, column revenue. There is no output-name field here; the result column is always named after the column and the function, so you get revenue_sum. If your data is already one row per salesperson, skip this and rank directly.

Pivot is a different tool. Reach for it when you want a second dimension spread across columns, one column per month or per region. A plain "total revenue by salesperson" has nothing to spread, so Group & Aggregate is the right step.

So the two-step version is: Group & Aggregate first, then Window Function with Order by set to revenue_sum (Largest first) for the ranking. The second step reads whatever the first one produced, which is why the Order by dropdown now offers the aggregated column instead of the raw one.

Start ranking your data →