CODEWITHSUNDEEP
mysqlsql
user1972031
user1972031

Reputation: 557

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

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>

Upvotes: 0

Views: 788

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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.

Upvotes: 1

Related Questions