ROW_NUMBER() equivalent in MySQL for inserting
i'm trying to convert SQL scripts that was created in Microsoft SQL Server to run with a link sever to scripts that can be used in SQL Procedures, the script i'm on uses
ROW_NUMBER() OVER(ORDER BY [FIELDS])
to create a primary key that isn't dependent on Auto Increment, when i try and save the code as a Procedure i get this error
ERROR 1064 (42000): 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 '(ORDER BY [FIELDS])' at line [LINENO]
obviously the error is saying that ROW_NUMBER OVER is not right cause i removed the OVER bit and got an error saying that ROW_NUMBER was undefined
everywhere i search i get nothing but people asking this question for SELECT statement, not INSERT statements and the answers most of the time are just about either getting the number of rows or getting the last id inserted, so what i can i use to create the same data that ROW_NUMBER() would in Microsoft Server
Answer
Unfortunately, there is no ROW_NUMBER() equivalent in MySQL but you can still simulate it by creating a simple variable which holds a value an increment it every row.
Example:
SET @rank=0;
SELECT @rank := @rank+1 AS rank, fruit, amount
FROM sales
ORDER BY amount DESC;