05 Jun

MySQL: Cheesy Way To Row Number

The classic way to create row numbers in MySQL is to use a variable and increment it for each row.  I did an example earlier showing it this way.  In SQL Server, you just use ROW_NUMBER() or one of its variants.  But, just for fun here is another way which was in the SQL Cookbook.

For this I’ll be using my good old military spending data.

mysql> select @ctr := @ctr + 1 AS rowCtr, region, country, spending
    -> FROM militaryspending
    -> JOIN (SELECT @ctr := 0) AS a
    -> ORDER BY spending DESC LIMIT 5;
+--------+---------------+----------------+----------+
| rowCtr | region        | country        | spending |
+--------+---------------+----------------+----------+
|      1 | North America | United States  |   711421 |
|      2 | Asia          | China          |   142859 |
|      3 | Asia          | Russia         |    71853 |
|      4 | Europe        | United Kingdom |    62685 |
|      5 | Europe        | France         |    62535 |
+--------+---------------+----------------+----------+
5 rows in set (0.00 sec)

Anyways, that is pretty easy. Suppose, however, that we want to see the last 5. We can do this, but it won’t number the way we, or I, want it to.

mysql> select @ctr := @ctr + 1 AS rowCtr, region, country, spending
    -> FROM militaryspending
    -> JOIN (SELECT @ctr := 0) AS a
    -> ORDER BY spending LIMIT 5;
+--------+---------------+------------+----------+
| rowCtr | region        | country    | spending |
+--------+---------------+------------+----------+
|      1 | Africa        | Seychelles |      9.3 |
|      2 | Africa        | Cape Verde |      9.7 |
|      3 | Africa        | Mauritius  |     10.1 |
|      4 | North America | Belize     |     15.7 |
|      5 | Europe        | Moldova    |     20.8 |
+--------+---------------+------------+----------+
5 rows in set (0.00 sec)

I, actually, wanted to see it numbered as 126, 125, 124, etc.

That’s actually easy to do, just do a subquery / inline view as follows:

mysql> SELECT rowCtr, region, country, spending
    -> FROM
    -> (
    -> select @ctr := @ctr + 1 AS rowCtr, region, country, spending
    -> FROM militaryspending
    -> JOIN (SELECT @ctr := 0) AS a
    -> ORDER BY spending DESC
    -> ) AS b
    -> ORDER BY rowCtr DESC LIMIT 5;
+--------+---------------+------------+----------+
| rowCtr | region        | country    | spending |
+--------+---------------+------------+----------+
|    126 | Africa        | Seychelles |      9.3 |
|    125 | Africa        | Cape Verde |      9.7 |
|    124 | Africa        | Mauritius  |     10.1 |
|    123 | North America | Belize     |     15.7 |
|    122 | Europe        | Moldova    |     20.8 |
+--------+---------------+------------+----------+
5 rows in set (0.00 sec)

The SQL Cookbook also had an interesting approach, they used a COUNT(*). It’s a weird solution because it can only work on an ordered list of some sort. For example, if we alphabetized our list of countries it would work as follows:

mysql> SELECT a.country,
    -> a.spending,
    -> (SELECT COUNT(*) FROM militaryspending WHERE country <= a.country) as rowCtr     
    -> FROM militaryspending a
    -> ORDER BY country LIMIT 5;
+-------------+----------+--------+
| country     | spending | rowCtr |
+-------------+----------+--------+
| Afghanistan |      878 |      1 |
| Albania     |      197 |      2 |
| Algeria     |     8665 |      3 |
| Angola      |     3647 |      4 |
| Argentina   |     3295 |      5 |
+-------------+----------+--------+
5 rows in set (0.01 sec)

I dunno, weird solution but it’s what he did, and it does work in some situations. I read that section really quick so maybe I missed something, something really obvious but I’ll stick to the variable for now.

Leave a Reply

Your email address will not be published. Required fields are marked *