lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

lesson10Title

lesson11Title

lesson12Title

sqlIntroAdvancedChapter3Title

sqlIntroAdvancedChapter1Title

sqlIntroAdvancedChapter2Title

-- Rank CodeFriends students within each class by score
SELECT name,
  class,
  score,
  RANK() OVER (
    PARTITION BY class
    ORDER BY score DESC
  ) AS rank_in_class
FROM scores;

# PARTITION BY

The `PARTITION BY` clause is used with window functions **to divide rows into groups**, with each group processed independently.

It works similarly to `GROUP BY`, but unlike `GROUP BY`, it does not collapse rows. Instead, it keeps all rows and logically segments them for window function calculations.

<br/>

## Syntax

Use `PARTITION BY` to divide rows into partitions.

```sql title="PARTITION BY syntax"
SELECT column,
  window_function(...) OVER (
    PARTITION BY group_column
    ORDER BY sort_column
  ) AS result
FROM table;
```

* `PARTITION BY` splits rows into groups.
* `ORDER BY` sorts rows within each group.

<br/>

## Example: Rank Students Within Each Class

Assume we have the following table:

*scores*

| name   | class | score |
| ------ | ----- | ----- |
| Alex   | A     | 92    |
| Sara   | A     | 95    |
| Daniel | A     | 88    |
| Mia    | B     | 90    |
| John   | B     | 85    |
| Emma   | B     | 93    |

<br/>

Suppose we want to rank students within each class by their score.

```sql title="Using PARTITION BY"
SELECT name,
  class,
  score,
  RANK() OVER (
    PARTITION BY class
    ORDER BY score DESC
  ) AS rank_in_class
FROM scores;
```

The query returns the following:

*Result:*

| name   | class | score | rank_in_class |
| ------ | ----- | ----- | --------------- |
| Sara   | A     | 95    | 1               |
| Alex   | A     | 92    | 2               |
| Daniel | A     | 88    | 3               |
| Emma   | B     | 93    | 1               |
| Mia    | B     | 90    | 2               |
| John   | B     | 85    | 3               |

In this result, each class has its own ranking system because of `PARTITION BY class`.

<br/>

## Why is `PARTITION BY` useful?

Use `PARTITION BY` when you want to:  

- Apply calculations **within groups** (e.g., ranks per class, totals per department)  
- Keep **individual rows visible** while still comparing them to peers  
- Perform **analytics and reporting** where context matters inside groups

The RANK() function assigns the same rank to rows with identical values and skips subsequent ranks, leading to gaps. In contrast, DENSE_RANK() assigns consecutive ranks without gaps, even if values are identical. This distinction is crucial when you need rankings that reflect ties differently.

PARTITION BY

Syntax

Example: Rank Students Within Each Class

Why is PARTITION BY useful?

What is the main difference between the RANK() and DENSE_RANK() functions in SQL?

Why is `PARTITION BY` useful?