CODEWITHSUNDEEP
mysqlwindow-functionsrow-number
Olajumoke Ademilade
Olajumoke Ademilade

Reputation: 191

How can I use MySQL ROW_NUMBER Function in a query

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?

Upvotes: 4

Views: 2860

Answers (2)

esims
esims

Reputation: 420

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.

Upvotes: 1

user11355039
user11355039

Reputation:

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)

Upvotes: 0

Related Questions