Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ROW_NUMBER() equivalent in MySQL for inserting [duplicate]

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

like image 214
Memor-X Avatar asked Aug 15 '12 03:08

Memor-X


People also ask

What is the equivalent of Rownum in MySQL?

MySQL doesn't support ROWNUM() function, but it since version 8.0, MySQL introduced ROW_NUMBER() function as an equivalent to return the number of the current row within its partition during data retrieval. Rows numbers range from 1 to the number of rows in the partition.

Is ROW_NUMBER () available in MySQL?

The ROW_NUMBER() function in MySQL is used to returns the sequential number for each row within its partition. It is a kind of window function. The row number starts from 1 to the number of rows present in the partition.

What is difference between Rownum and ROW_NUMBER?

ROWNUM is the sequential number, allocated to each returned row during query execution. ROW_NUMBER assigns a number to each row according to its ordering within a group of rows. ROW_NUMBER is a function that returns numeric value.

Is ROW_NUMBER faster than distinct?

In my experience, an aggregate (DISTINCT or GROUP BY) can be quicker then a ROW_NUMBER() approach.


1 Answers

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;
  • SQLFiddle Demo
like image 54
John Woo Avatar answered Oct 17 '22 05:10

John Woo