Home » SQL & PL/SQL » SQL & PL/SQL » Adding rownum in pagination query in oralce 12c (Oracle 12c)
Adding rownum in pagination query in oralce 12c [message #671711] Sat, 15 September 2018 06:39 Go to next message
sss111ind
Messages: 587
Registered: April 2012
Location: India
Senior Member

Hi All,

We have to use rownum as record no but it is not coming in sorted order. How to sort it, whether we have to rewirte the query.


select 
    emp.* ,rownum rn 
from 
   emp 
ORDER BY JOB OFFSET 7 ROWS FETCH NEXT 5 ROWS ONLY;

Regards,
Nathan
Re: Adding rownum in pagination query in oralce 12c [message #671712 is a reply to message #671711] Sat, 15 September 2018 07:20 Go to previous messageGo to next message
John Watson
Messages: 7557
Registered: January 2010
Location: Global Village
Senior Member
Column projection, which is when the rownum is assigned, occurs before ordering. You will need to project the rownum in an outer query against an inline subquery that does the ordering.
Re: Adding rownum in pagination query in oralce 12c [message #671713 is a reply to message #671712] Sat, 15 September 2018 07:54 Go to previous messageGo to next message
sss111ind
Messages: 587
Registered: April 2012
Location: India
Senior Member

Thanks John,

If we put rownum in an outer query then the new record no. will be generated as 1 to 5 not the actual rows what the inner query processing .How to get the actual record no i.e for the string ( OFFSET 7 ROWS FETCH NEXT 5 ROWS ONLY) as starting record no as 8 and end record no as 13. How the earlier top N query is very useful to get the sorted result as well as record no.

Regards,
Nathan

Re: Adding rownum in pagination query in oralce 12c [message #671714 is a reply to message #671713] Sat, 15 September 2018 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65765
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ROW_NUMBER

Re: Adding rownum in pagination query in oralce 12c [message #671715 is a reply to message #671714] Sat, 15 September 2018 08:58 Go to previous messageGo to next message
sss111ind
Messages: 587
Registered: April 2012
Location: India
Senior Member

Thanks Michel Sir,

select 
    emp.* ,row_number() over(order by job ) rn
from 
   emp 
ORDER BY JOB OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY;

Its working but 2 order by seems unncessary which concludes that old is gold.

Regards,
Nathan
Re: Adding rownum in pagination query in oralce 12c [message #671723 is a reply to message #671715] Sat, 15 September 2018 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65765
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from 
(select emp.* ,row_number() over(order by job ) rn from emp)
where rn between X and Y
/

[Updated on: Sat, 15 September 2018 12:36]

Report message to a moderator

Re: Adding rownum in pagination query in oralce 12c [message #671724 is a reply to message #671713] Sat, 15 September 2018 13:30 Go to previous messageGo to next message
John Watson
Messages: 7557
Registered: January 2010
Location: Global Village
Senior Member
You doidn't even try, did you?
pdby1> select e.*,rownum from (select * from emp order by job) e offset 7 rows fetch next 5 rows only;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO     ROWNUM
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 1981-04-02:00:00:00       2975                    20       8
      7782 CLARK      MANAGER         7839 1981-06-09:00:00:00       2450                    10       9
      7839 KING       PRESIDENT            1981-11-17:00:00:00       5000                    10      10
      7844 TURNER     SALESMAN        7698 1981-09-08:00:00:00       1500          0         30      11
      7654 MARTIN     SALESMAN        7698 1981-09-28:00:00:00       1250       1400         30      12

pdby1>
Re: Adding rownum in pagination query in oralce 12c [message #671741 is a reply to message #671724] Mon, 17 September 2018 03:58 Go to previous messageGo to next message
sss111ind
Messages: 587
Registered: April 2012
Location: India
Senior Member

Thanks All,

Nice solution John for 12c solution ,I tried but could not find the actual logic.

Regards,
Nathan

[Updated on: Mon, 17 September 2018 04:07]

Report message to a moderator

Re: Adding rownum in pagination query in oralce 12c [message #671742 is a reply to message #671741] Mon, 17 September 2018 04:00 Go to previous message
Michel Cadot
Messages: 65765
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I suggest you use EXPLAIN PLAN and see what comes when you query the result.

Previous Topic: Error in PL/SQL package function (table of records)
Next Topic: Time difference between 2 rows
Goto Forum:
  


Current Time: Sun Sep 23 03:51:56 CDT 2018