Recipe Tableau and SQL

Two values tie. Which rank function should I use?

When two values tie for second, RANK gives 1, 2, 2, 4; RANK_DENSE gives 1, 2, 2, 3; RANK_MODIFIED gives 1, 3, 3, 4; RANK_UNIQUE gives 1, 2, 3, 4. In SQL: RANK(), DENSE_RANK(), a COUNT(*) window and ROW_NUMBER(). The choice decides who makes a cut: in this climbing league, a 'top 6' final takes 4, 6, 7 or 10 climbers from the same scores, depending on the function.

Updated October 30, 2026

The data

Northwall climbing gym, autumn bouldering league, round 3

Synthetic dataclimbs.csv

48 climbers in two divisions try 8 boulders each. A top scores 25 points, a zone 10. The top 6 in each division go to the final, and scores in steps of 5 tie all the time.

`climber`	`division`	`boulder`	`result`	`points`	`attempts`
Aiko Quist	Open	B1	none	0	5
Aiko Quist	Open	B2	none	0	5
Aiko Quist	Open	B3	top	25	4
Aiko Quist	Open	B4	none	0	5

Step 1

Total each climber's points

Scores move in steps of 5, so flat runs of equal bars appear everywhere. Every flat run is a tie the rank function has to decide.

Tableau

SUM([points])
// Rows: division, climber; sorted by SUM(points) descending

SQL

SELECT division, climber, SUM(points) AS points
FROM input
GROUP BY division, climber
ORDER BY division, points DESC, climber

Rows

Division	Climber	Points
Masters	Jade Varga	200
Masters	Signe Jansen	185
Open	Wes Young	185
Open	Pavel Zielinski	175
Masters	Kian Okafor	170
Open	Lars Rossi	170
Open	Rosa Holm	170
Masters	Tomas Kowal	160
Masters	Zara Costa	160
Open	Nils Lind	160
Open	Ugo Grau	160
Open	Ximena Dahl	160
Open	Odette Toivonen	155
Masters	Bodhi Ito	145
Masters	Otto Toivonen	145
Masters	Remy Holm	145
Masters	Cyrus Berg	135
Masters	Hedda Ueda	135
Masters	Mateo Weiss	135
Open	Cleo Berg	135
Open	Kofi Okafor	135
Masters	Isak Sato	120
Open	Dario Novak	120
Open	Hana Ueda	120
Open	Juno Varga	120
Open	Vera Moreau	120
Open	Yusuf Costa	120
Masters	Gus Fontaine	115
Masters	Dina Novak	105
Masters	Elio Pires	105
Masters	Xander Dahl	105
Open	Femi Ekberg	105
Open	Greta Fontaine	105
Masters	Faye Ekberg	100
Open	Mira Weiss	100
Open	Sami Jansen	100
Masters	Una Grau	95
Open	Aiko Quist	95
Masters	Viggo Moreau	80
Open	Tess Kowal	80
Masters	Alba Quist	60
Masters	Nell Lind	60
Open	Ilya Sato	60
Masters	Wanda Young	55
Masters	Priya Zielinski	45
Open	Bram Ito	45
Open	Esme Pires	45
Masters	Lotte Rossi	35

Step 2

Rank the same totals four ways

Look at the three Open climbers on 160. RANK puts them all 5th and skips to 8th. RANK_DENSE puts them 4th and carries on at 5th. RANK_MODIFIED puts them 7th, the number of climbers at or above them. RANK_UNIQUE gives 5, 6 and 7 in alphabetical order. COUNT(*) works as the modified rank because a window with ORDER BY counts every row tied with the current one.

Tableau

RANK(SUM([points]), 'desc')   RANK_DENSE(SUM([points]), 'desc')   RANK_MODIFIED(SUM([points]), 'desc')   RANK_UNIQUE(SUM([points]), 'desc')
// Compute Using: climber

SQL

WITH t AS (
  SELECT division, climber, SUM(points) AS points
  FROM input
  GROUP BY division, climber
)
SELECT
  division,
  climber,
  points,
  RANK() OVER w AS rank,
  DENSE_RANK() OVER w AS rank_dense,
  COUNT(*) OVER w AS rank_modified,
  ROW_NUMBER() OVER (PARTITION BY division ORDER BY points DESC, climber) AS rank_unique
FROM t
WINDOW w AS (PARTITION BY division ORDER BY points DESC)
ORDER BY division, points DESC, climber

Rows

Division	Climber	Points	Rank	Rank dense	Rank modified	Rank unique
Masters	Jade Varga	200	1	1	1	1
Masters	Signe Jansen	185	2	2	2	2
Masters	Kian Okafor	170	3	3	3	3
Masters	Tomas Kowal	160	4	4	5	4
Masters	Zara Costa	160	4	4	5	5
Masters	Bodhi Ito	145	6	5	8	6
Masters	Otto Toivonen	145	6	5	8	7
Masters	Remy Holm	145	6	5	8	8
Masters	Cyrus Berg	135	9	6	11	9
Masters	Hedda Ueda	135	9	6	11	10
Masters	Mateo Weiss	135	9	6	11	11
Masters	Isak Sato	120	12	7	12	12
Masters	Gus Fontaine	115	13	8	13	13
Masters	Dina Novak	105	14	9	16	14
Masters	Elio Pires	105	14	9	16	15
Masters	Xander Dahl	105	14	9	16	16
Masters	Faye Ekberg	100	17	10	17	17
Masters	Una Grau	95	18	11	18	18
Masters	Viggo Moreau	80	19	12	19	19
Masters	Alba Quist	60	20	13	21	20
Masters	Nell Lind	60	20	13	21	21
Masters	Wanda Young	55	22	14	22	22
Masters	Priya Zielinski	45	23	15	23	23
Masters	Lotte Rossi	35	24	16	24	24
Open	Wes Young	185	1	1	1	1
Open	Pavel Zielinski	175	2	2	2	2
Open	Lars Rossi	170	3	3	4	3
Open	Rosa Holm	170	3	3	4	4
Open	Nils Lind	160	5	4	7	5
Open	Ugo Grau	160	5	4	7	6
Open	Ximena Dahl	160	5	4	7	7
Open	Odette Toivonen	155	8	5	8	8
Open	Cleo Berg	135	9	6	10	9
Open	Kofi Okafor	135	9	6	10	10
Open	Dario Novak	120	11	7	15	11
Open	Hana Ueda	120	11	7	15	12
Open	Juno Varga	120	11	7	15	13
Open	Vera Moreau	120	11	7	15	14
Open	Yusuf Costa	120	11	7	15	15
Open	Femi Ekberg	105	16	8	17	16
Open	Greta Fontaine	105	16	8	17	17
Open	Mira Weiss	100	18	9	19	18
Open	Sami Jansen	100	18	9	19	19
Open	Aiko Quist	95	20	10	20	20
Open	Tess Kowal	80	21	11	21	21
Open	Ilya Sato	60	22	12	22	22
Open	Bram Ito	45	23	13	24	23
Open	Esme Pires	45	23	13	24	24

How many climbers make a top-6 final?

Anywhere from 4 to 10, from the same scores. In the Open division, RANK sends 7 climbers to the final, RANK_DENSE 10, RANK_MODIFIED 4, and only RANK_UNIQUE exactly 6. Pick a function and a number of spots: with RANK and 6 spots, 7 Open and 8 Masters climbers make it.

Rank function

Final spots per division6310

Step 3

Draw the line for the final

Switch the rank function and watch the line move. The scores never change; only the rule for ties does.

Tableau

IF RANK(SUM([points]), 'desc') <= 6 THEN "Final"
ELSE "Out"
END
// Compute Using: climber

SQL

WITH t AS (
  SELECT division, climber, SUM(points) AS points
  FROM input
  GROUP BY division, climber
), k AS (
  SELECT
    *,
    RANK() OVER w AS r1,
    DENSE_RANK() OVER w AS r2,
    COUNT(*) OVER w AS r3,
    ROW_NUMBER() OVER (PARTITION BY division ORDER BY points DESC, climber) AS r4
  FROM t
  WINDOW w AS (PARTITION BY division ORDER BY points DESC)
)
SELECT
  division,
  climber,
  points,
  CASE
    WHEN (CASE '{knob.method}' WHEN 'RANK' THEN r1 WHEN 'RANK_DENSE' THEN r2 WHEN 'RANK_MODIFIED' THEN r3 ELSE r4 END) <= 6 THEN 'Final'
    ELSE 'Out'
    END AS status
FROM k
ORDER BY division, points DESC, climber

Rows

Division	Climber	Points	Status
Masters	Jade Varga	200	Final
Masters	Signe Jansen	185	Final
Open	Wes Young	185	Final
Open	Pavel Zielinski	175	Final
Masters	Kian Okafor	170	Final
Open	Lars Rossi	170	Final
Open	Rosa Holm	170	Final
Masters	Tomas Kowal	160	Final
Masters	Zara Costa	160	Final
Open	Nils Lind	160	Final
Open	Ugo Grau	160	Final
Open	Ximena Dahl	160	Final
Open	Odette Toivonen	155	Out
Masters	Bodhi Ito	145	Final
Masters	Otto Toivonen	145	Final
Masters	Remy Holm	145	Final
Masters	Cyrus Berg	135	Out
Masters	Hedda Ueda	135	Out
Masters	Mateo Weiss	135	Out
Open	Cleo Berg	135	Out
Open	Kofi Okafor	135	Out
Masters	Isak Sato	120	Out
Open	Dario Novak	120	Out
Open	Hana Ueda	120	Out
Open	Juno Varga	120	Out
Open	Vera Moreau	120	Out
Open	Yusuf Costa	120	Out
Masters	Gus Fontaine	115	Out
Masters	Dina Novak	105	Out
Masters	Elio Pires	105	Out
Masters	Xander Dahl	105	Out
Open	Femi Ekberg	105	Out
Open	Greta Fontaine	105	Out
Masters	Faye Ekberg	100	Out
Open	Mira Weiss	100	Out
Open	Sami Jansen	100	Out
Masters	Una Grau	95	Out
Open	Aiko Quist	95	Out
Masters	Viggo Moreau	80	Out
Open	Tess Kowal	80	Out
Masters	Alba Quist	60	Out
Masters	Nell Lind	60	Out
Open	Ilya Sato	60	Out
Masters	Wanda Young	55	Out
Masters	Priya Zielinski	45	Out
Open	Bram Ito	45	Out
Open	Esme Pires	45	Out
Masters	Lotte Rossi	35	Out

Step 4

Count the finalists under each rule

Only the unique rank always fills exactly the spots on offer. RANK lets in everyone tied on the last place, RANK_MODIFIED shuts them all out, and RANK_DENSE can let in far more than the cutoff, because it counts distinct scores, not people.

Tableau

WINDOW_SUM(IF RANK(SUM([points]), 'desc') <= 6 THEN 1 ELSE 0 END)
// one calc per rank function

SQL

WITH t AS (
  SELECT division, climber, SUM(points) AS points
  FROM input
  GROUP BY division, climber
), k AS (
  SELECT
    *,
    RANK() OVER w AS r1,
    DENSE_RANK() OVER w AS r2,
    COUNT(*) OVER w AS r3,
    ROW_NUMBER() OVER (PARTITION BY division ORDER BY points DESC, climber) AS r4
  FROM t
  WINDOW w AS (PARTITION BY division ORDER BY points DESC)
)
SELECT
  division,
  m.method,
  COUNT(*) FILTER (WHERE CASE m.method WHEN 'RANK' THEN r1 WHEN 'RANK_DENSE' THEN r2 WHEN 'RANK_MODIFIED' THEN r3 ELSE r4 END <= 6) AS finalists
FROM k CROSS JOIN (VALUES ('RANK'), ('RANK_DENSE'), ('RANK_MODIFIED'), ('RANK_UNIQUE')) AS m(method)
GROUP BY division, m.method
ORDER BY division, m.method

Rows

Division	Method	Finalists
Masters	RANK	8
Masters	RANK_DENSE	11
Masters	RANK_MODIFIED	5
Masters	RANK_UNIQUE	6
Open	RANK	7
Open	RANK_DENSE	10
Open	RANK_MODIFIED	4
Open	RANK_UNIQUE	6

Which rank should I use?

Use RANK when tied people should share a place, RANK_DENSE when you want score tiers, and a unique rank when the list must have exactly N rows. A unique rank needs a tie-break that means something; the alphabet is not one.

Step 5

Break ties with a rule that means something

Climbing breaks ties on attempts: fewer tries for the same score ranks higher. Tableau's rank functions take one expression, so fold the tie-break into it: points times 1,000, minus attempts, keeps points first. In SQL, add attempts as a second sort key, and the name last so the order is always the same.

Tableau

RANK_UNIQUE(SUM([points]) * 1000 - SUM([attempts]), 'desc')
// Compute Using: climber

SQL

WITH t AS (
  SELECT
    division,
    climber,
    SUM(points) AS points,
    SUM(attempts) AS attempts
  FROM input
  GROUP BY division, climber
)
SELECT
  division,
  climber,
  points,
  attempts,
  ROW_NUMBER() OVER (PARTITION BY division ORDER BY points DESC, attempts ASC, climber) AS place,
  CASE
    WHEN ROW_NUMBER() OVER (PARTITION BY division ORDER BY points DESC, attempts ASC, climber) <= 6 THEN 'Final'
    ELSE 'Out'
    END AS status
FROM t
ORDER BY division, place

Rows

Division	Climber	Points	Attempts	Place	Status
Masters	Jade Varga	200	23	1	Final
Masters	Signe Jansen	185	21	2	Final
Open	Wes Young	185	16	1	Final
Open	Pavel Zielinski	175	24	2	Final
Masters	Kian Okafor	170	23	3	Final
Open	Rosa Holm	170	16	3	Final
Open	Lars Rossi	170	26	4	Final
Masters	Tomas Kowal	160	23	4	Final
Masters	Zara Costa	160	30	5	Final
Open	Nils Lind	160	22	5	Final
Open	Ugo Grau	160	26	6	Final
Open	Ximena Dahl	160	28	7	Out
Open	Odette Toivonen	155	25	8	Out
Masters	Remy Holm	145	20	6	Final
Masters	Bodhi Ito	145	28	7	Out
Masters	Otto Toivonen	145	31	8	Out
Masters	Hedda Ueda	135	25	9	Out
Masters	Mateo Weiss	135	25	10	Out
Masters	Cyrus Berg	135	26	11	Out
Open	Cleo Berg	135	27	9	Out
Open	Kofi Okafor	135	27	10	Out
Masters	Isak Sato	120	29	12	Out
Open	Hana Ueda	120	23	11	Out
Open	Yusuf Costa	120	28	12	Out
Open	Juno Varga	120	29	13	Out
Open	Vera Moreau	120	29	14	Out
Open	Dario Novak	120	31	15	Out
Masters	Gus Fontaine	115	24	13	Out
Masters	Xander Dahl	105	22	14	Out
Masters	Dina Novak	105	26	15	Out
Masters	Elio Pires	105	28	16	Out
Open	Greta Fontaine	105	25	16	Out
Open	Femi Ekberg	105	33	17	Out
Masters	Faye Ekberg	100	28	17	Out
Open	Mira Weiss	100	29	18	Out
Open	Sami Jansen	100	30	19	Out
Masters	Una Grau	95	27	18	Out
Open	Aiko Quist	95	33	20	Out
Masters	Viggo Moreau	80	32	19	Out
Open	Tess Kowal	80	30	21	Out
Masters	Alba Quist	60	34	20	Out
Masters	Nell Lind	60	34	21	Out
Open	Ilya Sato	60	32	22	Out
Masters	Wanda Young	55	34	22	Out
Masters	Priya Zielinski	45	33	23	Out
Open	Bram Ito	45	36	23	Out
Open	Esme Pires	45	36	24	Out
Masters	Lotte Rossi	35	34	24	Out

Common mistake

A unique rank with an arbitrary tie-break

Three Masters climbers tie on 145 for 6th. Broken alphabetically, the last final spot goes to Bodhi Ito, who used 28 attempts. Remy Holm, on the same points in 20 attempts, is cut. A unique rank is only as fair as its tie-break, and in SQL ROW_NUMBER() without one can even change between runs.

WrongStarts with Jade Varga, Signe Jansen, Kian Okafor…

Point at the line to read it.

Tableau

RANK_UNIQUE(SUM([points]), 'desc')
// ties fall in name order

SQL

WITH t AS (
  SELECT
    division,
    climber,
    SUM(points) AS points,
    SUM(attempts) AS attempts
  FROM input
  GROUP BY division, climber
)
SELECT
  climber,
  points,
  attempts,
  ROW_NUMBER() OVER (PARTITION BY division ORDER BY points DESC, climber) AS place
FROM t
WHERE division = 'Masters'
ORDER BY place
LIMIT 8

RightStarts with Jade Varga, Signe Jansen, Kian Okafor…

Point at the line to read it.

Tableau

RANK_UNIQUE(SUM([points]) * 1000 - SUM([attempts]), 'desc')

SQL

WITH t AS (
  SELECT
    division,
    climber,
    SUM(points) AS points,
    SUM(attempts) AS attempts
  FROM input
  GROUP BY division, climber
)
SELECT
  climber,
  points,
  attempts,
  ROW_NUMBER() OVER (PARTITION BY division ORDER BY points DESC, attempts ASC, climber) AS place
FROM t
WHERE division = 'Masters'
ORDER BY place
LIMIT 8

Why does each division get fewer finalists than it should?

The rank ran over the whole table, not within each division.

Common mistake

Ranking across the whole table instead of per division

With Compute Using set to Table (down), Tableau ranks all 48 climbers together, so 'top 6' is the top 6 overall: 4 Open climbers and 3 Masters, ties included. Compute Using climber restarts the rank in each division.

WrongStarts with Masters, Open…

Point at a bar to read it.

Tableau

RANK(SUM([points]), 'desc')
// Compute Using: Table (down)

SQL

WITH t AS (
  SELECT division, climber, SUM(points) AS points
  FROM input
  GROUP BY division, climber
), k AS (
  SELECT *, RANK() OVER (ORDER BY points DESC) AS place
  FROM t
)
SELECT
  division,
  COUNT(*) FILTER (WHERE place <= 6) AS finalists
FROM k
GROUP BY division
ORDER BY division

RightStarts with Masters, Open…

Point at a bar to read it.

Tableau

RANK(SUM([points]), 'desc')
// Compute Using: climber

SQL

WITH t AS (
  SELECT division, climber, SUM(points) AS points
  FROM input
  GROUP BY division, climber
), k AS (
  SELECT
    *,
    RANK() OVER (PARTITION BY division ORDER BY points DESC) AS place
  FROM t
)
SELECT
  division,
  COUNT(*) FILTER (WHERE place <= 6) AS finalists
FROM k
GROUP BY division
ORDER BY division

Run it on your own data

Any table with an item, an optional group and a score works. Drop your CSV and pick the columns; the steps above recompute in your browser, and nothing is uploaded.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, climbs.csv, shows the shape.

Questions

Which rank should a leaderboard use?

RANK (competition rank, 1, 2, 2, 4) for most leaderboards: tied people share a place and the next place shows how many are ahead. Use RANK_DENSE when you want tiers, not places. Use a unique rank only with a tie-break that means something.

What is the difference between INDEX() and RANK_UNIQUE()?

INDEX() numbers rows in the order the view happens to sort them. RANK_UNIQUE sorts by the expression inside it, whatever the view's sort.

How do I rank within each group?

Rank along the item and restart for every group. In Tableau, set Compute Using to the item (climber here) so the other dimension (division) partitions it. In SQL, PARTITION BY division.

What about percentile ranks?

Tableau's RANK_PERCENTILE sorts ascending by default, so the highest value gets the top percentile. SQL's PERCENT_RANK() is (RANK - 1) / (rows - 1) within the partition. Both run from 0 to 1, and tied values share one value.

Two totals look equal but rank differently. Why?

They differ in a decimal place the view doesn't show. Round inside the rank, e.g. RANK(ROUND(SUM([amount]), 2)), if equal-looking totals should tie.

Sources

Tableau: Table calculation functions checked October 30, 2026
DuckDB: Window functions checked October 30, 2026