--test table 생성
CREATE TABLE ROWNUM_T1
(
C1 NUMBER,
C2 VARCHAR2(10),
C3 NUMBER
);
INSERT INTO ROWNUM_T1 VALUES(1,'C',101);
INSERT INTO ROWNUM_T1 VALUES(2,'D',102);
INSERT INTO ROWNUM_T1 VALUES(3,'E',103);
INSERT INTO ROWNUM_T1 VALUES(4,'F',104);
INSERT INTO ROWNUM_T1 VALUES(5,'G',105);
INSERT INTO ROWNUM_T1 VALUES(6,'H',106);
INSERT INTO ROWNUM_T1 VALUES(7,'I',107);
INSERT INTO ROWNUM_T1 VALUES(8,'J',108);
INSERT INTO ROWNUM_T1 VALUES(9,'K',109);
INSERT INTO ROWNUM_T1 VALUES(10,'L',110);
commit;
--SQL[1]
SELECT *
FROM ( SELECT *
FROM ROWNUM_T1
ORDER BY C1)
WHERE ROWNUM >= 1
AND ROWNUM <= 5;
--결과
C1 C2 C3
---------- -------------------- ----------
1 C 101
2 D 102
3 E 103
4 F 104
5 G 105
--SQL[2]
SELECT *
FROM ( SELECT *
FROM ROWNUM_T1
ORDER BY C1)
WHERE ROWNUM >= 6
AND ROWNUM <= 10;
--결과
no rows selected
--SQL[2]
SELECT *
FROM ( SELECT *
FROM ROWNUM_T1
ORDER BY C1)
WHERE ROWNUM >= 6
AND ROWNUM <= 10;
--결과
no rows selected
--SQL[2-1] : SQL[2]를 작성한 의도
SELECT C1,C2,C3,RNUM
FROM (
SELECT ROWNUM RNUM, t1.*
FROM (SELECT * FROM ROWNUM_T1 ORDER BY C1) t1
)
WHERE RNUM >= 6 AND RNUM <= 10;
---------------------------------------
--SQL[2-2] : SQL[2-1]과 결과가 다를까?
SELECT C1,C2,C3,RNUM
FROM (
SELECT ROWNUM RNUM, ROWNUM_T1.*
FROM ROWNUM_T1
)
WHERE RNUM >= 6 AND RNUM <= 10;