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;
It is still possible in a single SQL.
SELECT @rownum:=@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, mytable t;
posted by Mark Malakanov at 6:47 AM
11 Comments:
Thanks, useful for me.
does not work with a view...
really useful.....
thanks a lot!
nice very help full
Thank you very much! Very useful.
Top stuff.....thanks
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.
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
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”;
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
Post a Comment
<< Home