Get The Sequence Number of Rows In The Result of SELECT Query [MySQL]

ORACLE database provides ROWNUM to keep track of the no of rows produced in SELECT query. But in MySQL there is no default ROWNUM functionality. Here is a way how to do it.

ORACLE Implementation:


select rownum, name, sal from emp1;

ORACLE O/P:

ROWNUM NAME SAL
1 a 10000
2 b 5000
3 c 2000
4 d 1000
5 e 7000
6 f 4000

MySQL Implementation:


select @rownum:=@rownum+1 rownum, name, sal from emp1, (SELECT @rownum:=0) r;

MySQL O/P:

rownum name sal
1 a 10000
2 b 5000
3 c 2000
4 d 1000
5 e 7000
6 f 4000

So here in MySQL you need a variable to implement the ROWNUM feature of ORACLE.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s