Sunday, April 30, 2006

Oracle-like ROWNUM in MySQL

It needs sometimes to exactly mimic Oracle's ROWNUM where is no possibility to initiate a counter in previous statement by SET @rownum:=0;.

It is still possible in a single SQL.

SELECT @rownum:=@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, mytable t;

15 Comments:

Blogger alanGalan said...

Thanks, useful for me.

3:11 PM  
Blogger Sam X said...

does not work with a view...

8:25 AM  
Blogger Kalpesh said...

really useful.....

2:41 PM  
Blogger DoPPler said...

thanks a lot!

7:14 AM  
Blogger Mayur Kotak said...

nice very help full

9:10 AM  
Blogger Vince Cabrera said...

Thank you very much! Very useful.

11:24 AM  
Blogger Beebee31 said...

Top stuff.....thanks

8:42 AM  
Blogger vfdvgf said...

you can get Wow Power Leveling and wow gold wow power leveling

8:58 PM  
Blogger su-joe said...

Nice tip. How can we use this in update query

UPDATE test t
SET CREATED_BY=301 , CREATION_DATE=NOW()
WHERE
AND DOMAIN_ID=0
AND REJECT_COUNT=0
ORDER BY Q_CREATION_DATE
--@rownum<10; want to limit the result set which is ordered by date.

11:40 AM  
Blogger Nate said...

Just use LIMIT:

UPDATE test t
SET CREATED_BY=301 , CREATION_DATE=NOW()
WHERE
AND DOMAIN_ID=0
AND REJECT_COUNT=0
ORDER BY Q_CREATION_DATE
LIMIT 10

9:52 AM  
Blogger pbsl said...

you have a nice site. thanks for sharing this valuable resources. keep it up. anyway, various kinds of ebooks are available here

http://feboook.blogspot.com

8:51 AM  
Blogger Jessica DeLine said...

this works great! I used it and it works great in mysql to update place in my runner tracking database. I can't get it to run via php script though

$sql = “UPDATE Logs SET Place = (SELECT @rownum:=@rownum+1 rownum FROM (SELECT @rownum:=0) r) WHERE ElapsedTime !=’00:00:00′ and CheckpointID = ‘$CheckPointID’ Order by ElapsedTime”;

1:48 PM  
Blogger Justin said...

Oh this contribution is extremely interesting. I will test it when I arrive to my house. Thank you very much for it. Viagra Generic Viagra

1:01 PM  
Blogger Den said...

I was interested know about it.
bondage porn video

12:44 AM  
Blogger mahakk01 said...

ROWNUM in MySQl is used where no possibility to initiate a counter in previous statement. The syntax for the same is given in the post which is very easy to apply. Check it whether its working properly or not.This is useful for me. I really like your work.
sap support pack stacks

12:06 AM  

Post a Comment

<< Home