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*, allowing each group to be processed independently.

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

<br/>

## Syntax

You can use `PARTITION BY` to group 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/>

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               |

Here, each class has its own ranking system because of `PARTITION BY class`.

<br/>

In general, `PARTITION BY` splits rows into groups so window functions calculate results within each group rather than across the entire table.

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

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