SQL Rownum Pseudo Column

SQL Rownum Pseudo Column

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 br=""> 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 br=""> SQL>select 2+3 from emp where rownum<2 br="">

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);