what is the alternative for ROWNUM in MySQL in query?

Question

Hello when I'm executed the below query in oracle it works fine but in MySQL it is showing error for ROWNUM.

SELECT ROWNUM , key_value , new_rec 
FROM table1 
WHERE NOT EXISTS ( SELECT key_value 
                   FROM table2 
                   WHERE table1.key_value = table2.key_value 
                 );

what is the alternative for for this in MySQL.

score 2 · Answer 1 · answered Apr 06 '20 at 13:14

As a starter: without an ORDER BY, it is undefined which row will get which ROWNUM.

Now, assuming that you do have a sorting column (say id), it depends on the version of MySQL that you are running.

In MySQL 8.0, just use row_number():

SELECT row_number() over(order by id) rn, key_value, new_rec 
FROM table1 
WHERE NOT EXISTS ( SELECT key_value FROM table2 WHERE table1.key_value = table2.key_value );

In earlier versions, you would use user variables.

SET @rn := 0;
SELECT @rn := @rn + 1 rn, t.*
FROM (
    SELECT key_value , new_rec 
    FROM table1 
    WHERE NOT EXISTS ( SELECT key_value FROM table2 WHERE table1.key_value = table2.key_value )
    ORDER BY id
) t

score 0 · Answer 2 · answered Apr 06 '20 at 13:14

ROWNUM represent sequence value so, You can use row_number() to create unique sequence :

SELECT ROW_NUMBER() OVER (ORDER BY ?) AS ROWNUM , key_value , new_rec 
FROM table1 
WHERE NOT EXISTS ( SELECT key_value 
                   FROM table2 
                   WHERE table1.key_value = table2.key_value 
                 );

Note : Use identity column insstead of ?.

what is the alternative for ROWNUM in MySQL in query?

2 Answers2