//
you're reading...
Database, IT-Center, Oracle

Oracle select limit using rownum and rank()

**USING ROWNUM

Oracle has a system attribute ROWNUM for each record returned. A query that only returns the first 20 records would look like the one in the example.

ROWNUM starts counting with 1.

// mysql
select col from tbl limit 20;

// Oracle
select col from tbl where rownum<=20;

// Microsoft SQL
select top 20 col from tbl;

I found unique query for Oracle data pagination

select * from (
select p.*,rownum r from project_task_detail p where rownum <=9
)A where r >=7

If using rownum without alias “r” that will getting no result so alias for rownum must be defined to handle ambiguous “rownum” field name

**USING RANK

SQL> SELECT EMPNO, ENAME, SAL  FROM EMP;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

14 rows selected.

SQL> SELECT * FROM (
  2          SELECT EMPNO, ENAME, SAL ,
  3                 RANK() OVER (ORDER BY SAL DESC , ROWNUM ASC )  RN
  4          FROM EMP )
  5  WHERE RN <11;

     EMPNO ENAME             SAL         RN
---------- ---------- ---------- ----------
      7839 KING             5000          1
      7788 SCOTT            3000          2
      7902 FORD             3000          3
      7566 JONES            2975          4
      7698 BLAKE            2850          5
      7782 CLARK            2450          6
      7499 ALLEN            1600          7
      7844 TURNER           1500          8
      7934 MILLER           1300          9
      7521 WARD             1250         10

10 rows selected.

SQL>  SELECT * FROM (
  2          SELECT EMPNO, ENAME, SAL ,
  3                  RANK() OVER (ORDER BY SAL DESC , ROWNUM ASC )  RN
  4          FROM EMP )
  5  WHERE RN >=11;

     EMPNO ENAME             SAL         RN
---------- ---------- ---------- ----------
      7654 MARTIN           1250         11
      7876 ADAMS            1100         12
      7900 JAMES             950         13
      7369 SMITH             800         14

SQL>

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

December 2011
M T W T F S S
« Nov   Jan »
 1234
567891011
12131415161718
19202122232425
262728293031  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: