MySQL Forums
Forum List  »  Oracle

Another ROWNUM problem
Posted by: Bart Degryse
Date: April 18, 2005 05:53AM

I have an Oracle query containing the rownum function which I can't seem to replace by the MySQL LIMIT function.
Basically my query sorts a table and then gets three records from it : one I specify by an ID, the record before and the record after. You can recreate this with

CREATE TABLE USERS2 (ID number(3), Login varchar2(3), Datum date);
INSERT INTO USERS2 VALUES (1, 'anb', TO_Date('1995-02-01', 'YYYY-MM-DD'));
INSERT INTO USERS2 VALUES (2, 'els', TO_Date('1995-09-01', 'YYYY-MM-DD'));
INSERT INTO USERS2 VALUES (3, 'joh', TO_Date('1995-06-01', 'YYYY-MM-DD'));
INSERT INTO USERS2 VALUES (4, 'jos', TO_Date('1995-08-28', 'YYYY-MM-DD'));
INSERT INTO USERS2 VALUES (5, 'nan', TO_Date('1994-10-04', 'YYYY-MM-DD'));
INSERT INTO USERS2 VALUES (6, 'pet', TO_Date('1994-05-30', 'YYYY-MM-DD'));

Running

SELECT U.ID, U.Login, U.Datum
FROM (SELECT ROWNUM Rij, ID FROM (SELECT ID FROM Users2 ORDER BY Datum)) S,
(SELECT Rij FROM (SELECT ROWNUM Rij, ID FROM (SELECT ID FROM Users2 ORDER BY Datum)) WHERE ID = 1) R,
Users2 U
WHERE S.Rij BETWEEN (R.Rij - 1) AND (R.Rij + 1) AND S.ID = U.ID
ORDER BY S.Rij

would give me this result :
ID LOGIN DATUM
5 nan 1994-10-04
1 anb 1995-02-01
3 joh 1995-06-01

My query uses ROWNUM. Since ROWNUM doesn't exist in MySQL I need to rewrite the query. If only I knew how...
Can someone help me please. Thanks !

Options: ReplyQuote


Subject
Views
Written By
Posted
Another ROWNUM problem
9069
April 18, 2005 05:53AM
3428
August 12, 2005 06:07PM
3140
August 16, 2005 02:19AM
3098
October 06, 2005 08:35AM
3153
December 01, 2005 08:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.