Provide code for dense rank and row number.

Question

Assisting AI · Accepted Answer

In SQL, `ROW_NUMBER()` assigns a unique sequential integer to rows within a partition, while `DENSE_RANK()` assigns a rank that does not skip numbers when there are ties.

Example using a sample table `scores`:

```sql
SELECT
    player,
    team,
    score,
    ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC) AS row_num,
    DENSE_RANK() OVER (PARTITION BY team ORDER BY score DESC) AS dense_rank
FROM scores;
```

Result:
```
player   team   score  row_num  dense_rank
Alice    A      100    1        1
Bob      A       95    2        2
Charlie  A       95    3        2
David    A       90    4        3
Eva      B       98    1        1
Frank    B       98    2        1
Grace    B       90    3        2
Henry    B       85    4        3
```

`ROW_NUMBER()` gives a unique number even for ties, whereas `DENSE_RANK()` gives the same rank to tied scores and does not skip subsequent ranks. These functions are useful for pagination, leaderboard generation, and ranking analyses.

Provide code for dense rank and row number.

💡 Model Answer

🎤 Get questions like this answered in real-time