Another ROWNUM problem
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 !