How can I use MySQL ROW_NUMBER Function in a query

Question

I am trying to learn about the MySQL ROW_NUMBER() function and how to use it to generate a sequential number for each row in a result set.

What I tried so far:

SELECT e.*, 
ROW_NUMBER() 
OVER(PARTITION BY e.examid ORDER BY e.examid) AS id 
from exam e

When I run this query, a series of errors were displayed:

3 errors were found during analysis.
An alias was previously found. (near "id" at position 68)
An alias was expected. (near " " at position 67)
Unexpected token. (near "id" at position 68)
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY examid ORDER BY examid) as id from exam LIMIT 0, 25' at line 1

How can I go about it?

[MySQL 8.0 supports ROW_NUMBER](https://stackoverflow.com/questions/1895110/row-number-in-mysql/46753800#46753800) — Lukasz Szozda, Apr 11 '19 at 15:42
And more to the point, MySQL < 8.0 does _not_ support ROW_NUMBER. What version are you using? `SELECT @@version;` — Bill Karwin, Apr 11 '19 at 15:42
So indeed, this explains why you can't use features that are not available before MySQL 8.0. — Bill Karwin, Apr 11 '19 at 16:09
https://stackoverflow.com/questions/26160970/how-to-generate-sequential-row-number-in-tsql/27150629#27150629 — Waqar, Oct 14 '20 at 12:09

esims · Answer 1 · 2019-11-19T05:30:51.357

1

I had the same problem. This syntax works in my case:

SELECT
    (ROW_NUMBER() OVER (ORDER BY id)) row_number, Field1, Field2
FROM
    myTable
ORDER BY
    id

Additional remark: select version() returns 10.3.20-MariaDB-log.

edited Nov 19 '19 at 05:30

answered Nov 15 '19 at 21:19

esims

327
3
18

parentheses - is all I had to add to give an alias. that's excellent, thanks! – Hebe Dec 04 '20 at 00:45

score 0 · Answer 2 · answered Apr 22 '19 at 23:43

0

Change the I'd to something else because the select * statement will return an id and it will conflict with that I'd (AS id)

answered Apr 22 '19 at 23:43

How can I use MySQL ROW_NUMBER Function in a query

2 Answers2