Rank and compare rows

TutorialApplies to: Unified engine deployments

Use window functions to keep detail rows while comparing related values.

Before you start

You need Workbench and a running SQL engine. Every example includes its own input rows. No catalog, files, credentials, or external data is needed. These queries create no stored tables. Copy the SQL into Workbench and run the statements in order. Compare the result with the table below the query. WITH names input data for one statement only. Each example can run on its own.

1. Number rows within groups

What is each row position within its team?

WITH scores(id, team, score) AS (VALUES (1, 'A', 30), (2, 'A', 20), (3, 'A', 20), (4, 'B', 40), (5, 'B', 10))
SELECT id, ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id) AS position FROM scores ORDER BY id;

Expected result

id	position
1	1
2	2
3	3
4	1
5	2

An ID tie-breaker makes row numbering stable. Change one thing: Use score ASC. Team A positions become 3, 1, and 2.

2. Rank tied values

How does rank handle a tie?

WITH scores(id, team, score) AS (VALUES (1, 'A', 30), (2, 'A', 20), (3, 'A', 20), (4, 'B', 40), (5, 'B', 10))
SELECT id, RANK() OVER (ORDER BY score DESC) AS rank FROM scores ORDER BY id;

Expected result

id	rank
1	2
2	3
3	3
4	1
5	5

RANK gives ties the same rank and leaves a gap after them. Change one thing: Use DENSE_RANK. ID 5 gets rank 4.

3. Use dense ranks

Can tied ranks have no gaps?

WITH scores(id, team, score) AS (VALUES (1, 'A', 30), (2, 'A', 20), (3, 'A', 20), (4, 'B', 40), (5, 'B', 10))
SELECT id, DENSE_RANK() OVER (ORDER BY score DESC) AS rank FROM scores ORDER BY id;

Expected result

id	rank
1	2
2	3
3	3
4	1
5	4

DENSE_RANK counts distinct ordering values. Change one thing: Order score ASC. IDs 1 through 5 get ranks 3, 2, 2, 4, and 1.

4. Keep the top two per group

Which rows are top two within each team?

WITH scores(id, team, score) AS (VALUES (1, 'A', 30), (2, 'A', 20), (3, 'A', 20), (4, 'B', 40), (5, 'B', 10))
SELECT id, team FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id) AS position FROM scores) ranked WHERE position <= 2 ORDER BY id;

Expected result

id	team
1	A
2	A
4	B
5	B

Filter a window result in an outer query. Change one thing: Use position = 1. Expect IDs 1 and 4.

5. Read the previous value

What score came before each row?

WITH scores(id, team, score) AS (VALUES (1, 'A', 30), (2, 'A', 20), (3, 'A', 20), (4, 'B', 40), (5, 'B', 10))
SELECT id, LAG(score) OVER (PARTITION BY team ORDER BY id) AS previous FROM scores ORDER BY id;

Expected result

id	previous
1	NULL
2	30
3	20
4	NULL
5	40

LAG reads an earlier row within the same partition. Change one thing: Use LEAD. Expect 20, 20, NULL, 10, NULL.

6. Calculate a running total

What is the cumulative score per team?

WITH scores(id, team, score) AS (VALUES (1, 'A', 30), (2, 'A', 20), (3, 'A', 20), (4, 'B', 40), (5, 'B', 10))
SELECT id, SUM(score) OVER (PARTITION BY team ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total FROM scores ORDER BY id;

Expected result

id	total
1	30
2	50
3	70
4	40
5	50

The frame includes the current row and every earlier row in the partition. Change one thing: Remove PARTITION BY team. Expect totals 30, 50, 70, 110, 120.

7. Calculate a moving average

What is the average of this row and the preceding row?

WITH scores(id, team, score) AS (VALUES (1, 'A', 30), (2, 'A', 20), (3, 'A', 20), (4, 'B', 40), (5, 'B', 10))
SELECT id, AVG(score) OVER (PARTITION BY team ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_average FROM scores ORDER BY id;

Expected result

id	moving_average
1	30
2	25
3	20
4	40
5	25

At the start of a partition the frame has only one row. Change one thing: Use CURRENT ROW as both frame bounds. Each average equals its score.

8. Read the final group value

What is the last score in each team?

WITH scores(id, team, score) AS (VALUES (1, 'A', 30), (2, 'A', 20), (3, 'A', 20), (4, 'B', 40), (5, 'B', 10))
SELECT id, LAST_VALUE(score) OVER (PARTITION BY team ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_score FROM scores ORDER BY id;

Expected result

id	last_score
1	20
2	20
3	20
4	10
5	10

The explicit full frame lets LAST_VALUE read the end of the partition. Change one thing: Use FIRST_VALUE. Team A returns 30 and team B returns 40.

Continue

Continue to Read JSON and nested values, or return to Learn Laminar.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Rank and compare rows

Before you start

1. Number rows within groups

2. Rank tied values

3. Use dense ranks

4. Keep the top two per group

5. Read the previous value

6. Calculate a running total

7. Calculate a moving average

8. Read the final group value

Continue

​Before you start

​1. Number rows within groups

​2. Rank tied values

​3. Use dense ranks

​4. Keep the top two per group

​5. Read the previous value

​6. Calculate a running total

​7. Calculate a moving average

​8. Read the final group value

​Continue

Before you start

1. Number rows within groups

2. Rank tied values

3. Use dense ranks

4. Keep the top two per group

5. Read the previous value

6. Calculate a running total

7. Calculate a moving average

8. Read the final group value

Continue