rownum in Oracle

Here in this post, we will talk about the ROWNUM keyword in Oracle

What is ROWNUM

• It is a pseudo column (not a real column) that is available in a query.
• ROWNUM is assigned the numbers 1, 2, 3, 4, … N, where N is the number of rows for the first, second, third…n records
• A ROWNUM value is not assigned permanently to a row.

Example

CREATE TABLE "EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
         REFERENCES "DEPT" ("DEPTNO") ENABLE
);
insert into emp   values( 800, 'Blake', 'MANAGER', 7839,  to_date('1-5-2007','dd-mm-yyyy'),  2850, null, 10  );
insert into emp  values(  801, 'Clark', 'MANAGER', 7839,  to_date('9-6-2008','dd-mm-yyyy'),  2450, null, 10  );
insert into emp  values(  802, 'Scott', 'ANALYST', 7566,  to_date('9-6-2012','dd-mm-yyyy'),  3000, null, 20  );
insert into emp  values(  803, 'TPM', 'ANALYST', 7566,  to_date('9-6-2017','dd-mm-yyyy'),  3000, null, null  );
insert into emp  values(  804, 'T1OM', 'ANALYST', 7567,  to_date('9-7-2017','dd-mm-yyyy'),  4000, null, 20  );
insert into emp  values(  805, 'TOM', 'ANALYST', 7567,  to_date('9-7-2017','dd-mm-yyyy'),  4090, null, null  );
commit;

How to use rownum in oracle

• We can use rownum to limit the rows fetched by the SQL
• Suppose in the above example, If I just want to see 2 rows from the query, I can use like below

We can also use rownum together with an order by clause to find the highest two salaries

SELECT *
FROM (SELECT *
FROM emp
ORDER BY salary DESC)
WHERE ROWNUM <= 3;

Rownum between 100 and 200 in oracle(oracle rownum between)

we can use the below query

SELECT a.*
FROM (SELECT ROWNUM rn, b.*
FROM ( SELECT *
FROM emp
ORDER BY salary dsc) b  where rn <=200) a
WHERE a.rn >= 100

you cannot use like rownum between 100 and 200

rownum=2 is not working in oracle

If you fire a query like this

select * from emp where rownum=2;

It will always return zero rows. The reason is

“The first row fetched from the query is given a ROWNUM of 1, which makes this condition false as ROWNUM = 1, and we’re looking for ROWNUM = 2, which is obviously not true. The second row to be fetched from query is now the first row, but ROWNUM is still 1, because it wasn’t assigned yet, so the condition is false again. This keeps happening for all rows, so none are returned.”

Also Reads
Lead Function in Oracle
RANK function in Oracle
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljoffsetfetch.html