How to add row number to an MySQL display using ROW_NUMBER()

Question

How can I use ROW_NUMBER() function (required) to display row numbers on Mysql table display?

mysql> select * from work;
+------+-----------+
| name | work_days |
+------+-----------+
| john |         5 |
| jane |         7 |
| jane |         2 |
| john |         3 |
+------+-----------+
4 rows in set (0.01 sec)

Without using ROW_NUMBER():

mysql> SELECT name, 
    -> AVG(work_days) AS workday_average, 
    -> COUNT(*) as count
    -> FROM work
    -> GROUP BY name
    -> HAVING workday_average > 2
    -> ORDER BY workday_average ASC, count DESC;
+------+-----------------+-------+
| name | workday_average | count |
+------+-----------------+-------+
| john |          4.0000 |     2 |
| jane |          4.5000 |     2 |
+------+-----------------+-------+
2 rows in set (0.00 sec)

Errors below when trying to add a row number column using ROW_NUMBER().

mysql> SELECT name, 
    -> ROW_NUMBER() over(PARTITION BY name ORDER BY work_days) as row_num,
    -> AVG(work_days) AS workday_average, 
    -> COUNT(*) as count
    -> FROM work
    -> GROUP BY name
    -> HAVING workday_average > 2
    -> ORDER BY workday_average ASC, count DESC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(PARTITION BY name ORDER BY work_days) as row_num,
AVG(work_days) AS workday_ave' at line 2
mysql>

As Tim said, it doesn't make sense, but it does work, given sufficiently new MariaDB: fiddle. — Amadan
– Amadan, Commented Jun 11, 2019 at 8:33
@user1972031 . . . You are getting a syntax error on OVER (PARTITION. That means that your database does not support window functions, so you cannot do what you want on this database. You can upgrade the database to a more recent version. — Gordon Linoff
– Gordon Linoff, Commented Jun 11, 2019 at 11:29
Mysql version is 8.0.15 linux:MYSQL$ mysql -V mysql Ver 8.0.15 for osx10.14 on x86_64 (Homebrew) — user1972031
– user1972031, Commented Jun 11, 2019 at 21:21

Tim Biegeleisen · Accepted Answer · 2019-06-11 08:31:49Z

1

Window functions evaluate after GROUP BY aggregation has happened, so it doesn't make much sense to use a partition on the name, since each record at that point would be guaranteed to have a distinct name. Most likely, you want something like this:

SELECT
    name, 
    ROW_NUMBER() OVER (ORDER BY AVG(work_days), COUNT(*) DESC) AS row_num,
    AVG(work_days) AS workday_average, 
    COUNT(*) AS count
FROM work
GROUP BY
    name
HAVING
   workday_average > 2
ORDER BY
    workday_average,
    count DESC;

But this of course assumes that you are using MySQL 8+. If not, then ROW_NUMBER won't be available.

answered Jun 11, 2019 at 8:31

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Amadan Over a year ago

As the error mentions MariaDB, MariaDB needs to be at least 10.2+.

user1972031 Over a year ago

Mysql version is 8.0.15. linux:MYSQL$ mysql -V mysql Ver 8.0.15 for osx10.14 on x86_64 (Homebrew). I thought 8.0.15 should be able to have ROW_NUMBER() function?

Tim Biegeleisen Over a year ago

Yes, it seems that you are running MySQL 8+.

user1972031 Over a year ago

@Amadan: Yes, Thanks for the help. It worked fine now after I upgraded Mysql to version 15.1 and MariaDB to version 10.3.15

Amadan Over a year ago

mysql -V reports the version of the MySQL client. The issue was that the server was a pre-10.2 MariaDB.

Collectives™ on Stack Overflow

How to add row number to an MySQL display using ROW_NUMBER()

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related