How do you number SELECT query results using SQL?
Tags: sql
Published: 2021-08-17 10:44:32

---
canonical_url: https://chuniversiteit.nl/programming/how-do-you-number-select-query-results-using-sql
---

> _Do SQL queries work in every database? I wouldn’t count on it. That’s why this article gives you several alternatives._

**Want to let number or rank your query results? Let your database do the hard work for you.**

Let’s say you have a `team` table in your database that stores the name and number of gold, silver, and bronze medals won for each team:

<table>
  <thead>
    <tr>
      <th colSpan="5">team</th>
    </tr>
    <tr>
      <th>id</th>
      <th>name</th>
      <th>gold</th>
      <th>silver</th>
      <th>bronze</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td >1</td>
      <td>Taiwan #1</td>
      <td >4</td>
      <td >2</td>
      <td >1</td>
    </tr>
    <tr>
      <td >2</td>
      <td>There was an attempt</td>
      <td >0</td>
      <td >0</td>
      <td >0</td>
    </tr>
    <tr>
      <td >4</td>
      <td>Team 4 Plays</td>
      <td >3</td>
      <td >2</td>
      <td >2</td>
    </tr>
    <tr>
      <td >5</td>
      <td>Five Gals</td>
      <td >3</td>
      <td >2</td>
      <td >2</td>
    </tr>
    <tr>
      <td >6</td>
      <td>Sixcess guaranteed</td>
      <td >5</td>
      <td >4</td>
      <td >1</td>
    </tr>
    <tr>
      <td >7</td>
      <td>We ate nine</td>
      <td >0</td>
      <td >6</td>
      <td >3</td>
    </tr>
    <tr>
      <td >8</td>
      <td>AMERICA F*** YEAH</td>
      <td >5</td>
      <td >4</td>
      <td >0</td>
    </tr>
  </tbody>
</table>

As you can see, this table is currently ordered by `id`. We can use the `ORDER BY` clause to request a version of this data that shows teams with the most gold, silver, and bronze medals first:

```sql
SELECT
  name,
  gold,
  silver,
  bronze
FROM
  team
ORDER BY
  gold   DESC,
  silver DESC,
  bronze DESC;
```

```txt
+----------------------+------+--------+--------+
| name                 | gold | silver | bronze |
+----------------------+------+--------+--------+
| Sixcess guaranteed   |    5 |      4 |      1 |
| AMERICA F*** YEAH    |    5 |      4 |      0 |
| Taiwan #1            |    4 |      2 |      1 |
| Team 4 plays         |    3 |      2 |      2 |
| Five Gals            |    3 |      2 |      2 |
| We ate nine          |    0 |      6 |      3 |
| There was an attempt |    0 |      0 |      0 |
+----------------------+------+--------+--------+
```

While this gives you all the information you need, it would be more convenient if the rows were also numbered. You could number them yourself, but why do that when you can do it just as easily using SQL?

## The recommended way to number rows
There are two so-called window functions, `ROW_NUMBER()` and `RANK()`, that can be used to number rows in SQL output.

The first window function, `ROW_NUMBER()`, simply assigns a unique number from 1 to whatever the number of rows in the query result is. The snippet below shows how `ROW_NUMBER()` can be used to number teams. Note that the `ORDER BY` clause has been moved to `ROW_NUMBER() OVER ()`.

This can be useful if the result is meant to represent a sequence or if you simply need a way to refer to each row that is not based on an actual ID.

```sql
SELECT
  ROW_NUMBER() OVER (
    ORDER BY
      gold   DESC,
      silver DESC,
      bronze DESC
  ) AS number,
  name,
  gold,
  silver,
  bronze
FROM
  team;
```

```txt
+--------+----------------------+------+--------+--------+
| number | name                 | gold | silver | bronze |
+--------+----------------------+------+--------+--------+
|      1 | Sixcess guaranteed   |    5 |      4 |      1 |
|      2 | AMERICA F*** YEAH    |    5 |      4 |      0 |
|      3 | Taiwan #1            |    4 |      2 |      1 |
|      4 | Team 4 plays         |    3 |      2 |      2 |
|      5 | Five Gals            |    3 |      2 |      2 |
|      6 | We ate nine          |    0 |      6 |      3 |
|      7 | There was an attempt |    0 |      0 |      0 |
+--------+----------------------+------+--------+--------+
```

The other window function, `RANK()`, ranks your rows using the ordering criteria that you define within the `OVER ()` part. This is particularly useful when the results represent a list from best to worst (as in this example) from largest to smallest, smallest to largest, etc.

```sql
SELECT
  RANK() OVER (
    ORDER BY
      gold   DESC,
      silver DESC,
      bronze DESC
  ) AS rank,
  name,
  gold,
  silver,
  bronze
FROM
  team;
```

```txt
+------+----------------------+------+--------+--------+
| rank | name                 | gold | silver | bronze |
+------+----------------------+------+--------+--------+
|    1 | Sixcess guaranteed   |    5 |      4 |      1 |
|    2 | AMERICA F*** YEAH    |    5 |      4 |      0 |
|    3 | Taiwan #1            |    4 |      2 |      1 |
|    4 | Team 4 plays         |    3 |      2 |      2 |
|    4 | Five Gals            |    3 |      2 |      2 |
|    6 | We ate nine          |    0 |      6 |      3 |
|    7 | There was an attempt |    0 |      0 |      0 |
+------+----------------------+------+--------+--------+
```

## ROW_NUMBER() in old versions of MySQL
`ROW_NUMBER()` should be widely supported by now. Nevertheless, there are still many older databases that do not support it yet. It wouldn’t surprise me if most of those are older versions of MySQL on shared hosting plans.

If that happens to be the case for you, there’s a workaround that you can use to emulate the functionality of `ROW_NUMBER()`.

The snippet below shows how you can do this. Note that it consists of two queries, which must both be executed, in the correct order.

It works by first initialising a numeric variable, which you can then increment and add to each record that you retrieve from the database.

```sql
SET @row_number = 0; 

SELECT 
    @row_number := @row_number + 1 AS number,
    name,
    gold,
    silver,
    bronze
FROM
    team
ORDER BY
    gold   DESC,
    silver DESC,
    bronze DESC;
```

```txt
+--------+----------------------+------+--------+--------+
| number | name                 | gold | silver | bronze |
+--------+----------------------+------+--------+--------+
|      1 | Sixcess guaranteed   |    5 |      4 |      1 |
|      2 | AMERICA F*** YEAH    |    5 |      4 |      0 |
|      3 | Taiwan #1            |    4 |      2 |      1 |
|      4 | Team 4 plays         |    3 |      2 |      2 |
|      5 | Five Gals            |    3 |      2 |      2 |
|      6 | We ate nine          |    0 |      6 |      3 |
|      7 | There was an attempt |    0 |      0 |      0 |
+--------+----------------------+------+--------+--------+
```

## A workaround for RANK()
`RANK()` is not as easy to emulate as `ROW_NUMBER()`, but it’s still quite doable. The example below shows how ranks can be generated by [joining](https://chuniversiteit.nl/programming/when-and-how-to-join-a-table-with-itself) the `team` table with itself.

First, the original `team` table is given an alias, `team_result`. This where most of the data in our query output will come from.

Then we join the original `team` table (now called `team_result`) with another instance of `team`, which we call `better_team`. The “better” part is defined in the `JOIN` condition as any _other_ team (`team_result.id != better_team.id`) that has won more gold, silver, and/or bronze medals (the entire part after the `AND`).

We can use `GROUP BY` and `COUNT(better_team.id)` to count how many better-performing competitors each team has. This gets us very close to what we want. However, because the best team obviously isn’t bested by any other team, this would make the numbering begin at 0. We can fix this by incrementing the value of `COUNT(better_team.id)` by 1.

Finally, we sort the results using the newly computed `rank` field to obtain the same results that we saw earlier when we used `RANK()`.

Easy! (Sort of.)

```sql
SELECT
  COUNT(better_team.id) + 1 AS rank,
  team_result.name,
  team_result.gold,
  team_result.silver,
  team_result.bronze
FROM
  team team_result
LEFT JOIN
  team better_team ON
  team_result.id != better_team.id
  AND
  (
      team_result.gold < better_team.gold
    OR
      team_result.gold = better_team.gold
      AND
      team_result.silver < better_team.silver
    OR
      team_result.gold = better_team.gold
      AND
      team_result.silver = better_team.silver
      AND
      team_result.bronze < better_team.bronze
  )
GROUP BY
  team_result.id
ORDER BY
  rank ASC;
```

```txt
+------+----------------------+------+--------+--------+
| rank | name                 | gold | silver | bronze |
+------+----------------------+------+--------+--------+
|    1 | Sixcess guaranteed   |    5 |      4 |      1 |
|    2 | AMERICA F*** YEAH    |    5 |      4 |      0 |
|    3 | Taiwan #1            |    4 |      2 |      1 |
|    4 | Team 4 plays         |    3 |      2 |      2 |
|    4 | Five Gals            |    3 |      2 |      2 |
|    6 | We ate nine          |    0 |      6 |      3 |
|    7 | There was an attempt |    0 |      0 |      0 |
+------+----------------------+------+--------+--------+
```