Saturday, 27 May 2017

rownum in SQL

pseudo column rownum  

ROWNUM :- ROWNUM is a pschedo column, which is used to restrict the output records
                        count.

1) rownum works for  < (less than) or <= (less than equal to) and also works for =1

Examples :-
SQL>SELECT * FROM EMP WHERE ROWNUM<=5
SQL>SELECT * FROM EMP WHERE ROWNUM<3;
SQL>SELECT * FROM EMP WHERE ROWNUM=1;


2) rownum does not work for  > (greater than) or >= (greater than equal to)

Examples:- No records will be displayed for > or >=

SQL>SELECT * FROM EMP WHERE ROWNUM>1; 
SQL>SELECT * FROM EMP WHERE ROWNUM>=2;
SQL>SELECT * FROM EMP WHERE ROWNUM=2;



3) As a row number with records of table

Example :-
SQL>SELECT ROWNUM,A.* FROM EMP A;


Note:- We can select > and >= records with the help of rownum by below method

4) Way to select records with "rownum=" or "rownum>="

Examples:-
SQL>select rn,empno,ename from (select rownum rn ,a.empno,a.ename from emp a )
where rn=5;

SQL>select rn,empno,ename from (select rownum rn ,a.empno,a.ename from emp a )
where rn>=5;



5) Calculation from any table with help of rownum

Examples :-We can perform calculation same as dual table from any table with the help of rownum
 
SQL>select sysdate from emp where rownum<2;
SQL>select 2+3 from emp where rownum<2;



Note :- You can perform any calculation with any table what you can do with dual table.

6) We can get the last n records with the help of rownum
Example :-
SQL>SELECT * FROM EMP
          MINUS
          SELECT * FROM EMP WHERE ROWNUM<(SELECT COUNT(*)-&n FROM EMP);


1 comment:

  1. Thanks for the given information you are providing content very usefull to users.
    Learn How to Work With Ref Cursor in ORACLE 12C For Beginner Actual cost of this course is 9,999/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.


    Hurry-Up Enroll Now and get Free Access to the Entire Course


    ReplyDelete

Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...