Transformations
Dense Rank
Adds a SQL DENSE_RANK() column: tied rows share a number, and the next number follows on with no gap.
When to use it
Use it when you want the distinct levels counted — price bands, grade tiers, 'the three best scores' — rather than the position in a list.
In a pipeline
File InputDense RankExport Excel
Settings
| Setting | Type | Required | Default or hint |
|---|---|---|---|
| New Column Name | text | Yes | Dense Rank |
| Insert After | select | — | End of table |
| Partition By | select | — | Whole table (no partition) |
| Order By | select | — | Order rows arrive in |
| Order Direction | select | — | asc |
Example
Score tiers in a class
- New Column Name=Tier
- Order By=Score
- Order Direction=Descending
| Pupil | Score |
|---|---|
| Ada | 95 |
| Ben | 95 |
| Cy | 88 |
| Dee | 70 |
| Pupil | Score | Tier |
|---|---|---|
| Ada | 95 | 1 |
| Ben | 95 | 1 |
| Cy | 88 | 2 |
| Dee | 70 | 3 |
Rank would have given Cy 3; Dense Rank gives 2, because it counts levels rather than positions.
Worth knowing
- Dense Rank and Rank differ only in the ties: Rank leaves gaps after them, Dense Rank does not.
- Partition By restarts the numbering for each group, and Order By decides the order within it.
- To keep only the top three levels, follow this node with Filter Rows on the new column.