오라클에서는 특정 게시물 목록을 페이징처리할라면
ROWNUM 을 이용한후 다시 한번 괄호를 씌우는등... 좀 너저분하게(?) 처리해야 합니다.
그런데, PostgreSQL 에서는 아래와 같이 그냥 껌(?)입니다. 따로 설명은 하지 않겠습니다.
(참고로 아래구문은 MySQL 에서도 동일하게 사용할수 있습니다)
SQL> select ename, job, sal from emp order by sal;
ename | job | sal
--------+-----------+------
SMITH | CLERK | 800
JAMES | CLERK | 950
ADAMS | CLERK | 1100
WARD | SALESMAN | 1250
MARTIN | SALESMAN | 1250
MILLER | CLERK | 1300
TURNER | SALESMAN | 1500
ALLEN | SALESMAN | 1600
CLARK | MANAGER | 2450
BLAKE | MANAGER | 2850
JONES | MANAGER | 2975
FORD | ANALYST | 3000
SCOTT | ANALYST | 3000
KING | PRESIDENT | 5000
(14 rows)
scott@[local]:5432:scottdb]
SQL> select ename, job, sal from emp order by sal limit 5;
ename | job | sal
--------+----------+------
SMITH | CLERK | 800
JAMES | CLERK | 950
ADAMS | CLERK | 1100
WARD | SALESMAN | 1250
MARTIN | SALESMAN | 1250
(5 rows)
scott@[local]:5432:scottdb]
SQL> select ename, job, sal from emp order by sal offset 0 limit 5
;
ename | job | sal
--------+----------+------
SMITH | CLERK | 800
JAMES | CLERK | 950
ADAMS | CLERK | 1100
WARD | SALESMAN | 1250
MARTIN | SALESMAN | 1250
(5 rows)
scott@[local]:5432:scottdb]
SQL> select ename, job, sal from emp order by sal offset 5 limit 5;
ename | job | sal
--------+----------+------
MILLER | CLERK | 1300
TURNER | SALESMAN | 1500
ALLEN | SALESMAN | 1600
CLARK | MANAGER | 2450
BLAKE | MANAGER | 2850
(5 rows)
SQL> select ename, job, sal from emp order by sal offset 10 limit 5;
ename | job | sal
-------+-----------+------
JONES | MANAGER | 2975
FORD | ANALYST | 3000
SCOTT | ANALYST | 3000
KING | PRESIDENT | 5000
(4 rows)
scott@[local]:5432:scottdb]
SQL>