> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Rank and compare rows

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

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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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](https://docs.e6data.com/transformation/learn/json-and-nested.md), or return to [Learn Laminar](https://docs.e6data.com/transformation/learn.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.