Home » SQL & PL/SQL » SQL & PL/SQL » rownum and order by (Oracle 12)
rownum and order by [message #641308] Thu, 13 August 2015 03:50 Go to next message
mats-larsson@iname.com
Messages: 13
Registered: September 2013
Location: Stockholm
Junior Member
In the query below, am I ensured that I get the rows ordered by id_pers?
though the sort is in the inner select.
The purose is to get the first rows ordered by id_pers and limited by number of rows.

SELECT To_char(k.id_pers) id_pers 
FROM   (SELECT id_pers 
        FROM   t_konto_att_avstamma 
        ORDER  BY id_pers) k 
WHERE  k.id_pers > :sql_last_processed_id 
       AND ROWNUM <= :sql_number_to_process


The table t_konto_att_avstamma is index organized.

Re: rownum and order by [message #641313 is a reply to message #641308] Thu, 13 August 2015 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The purose is to get the first rows ordered by id_pers and limited by number of rows.


And you have it, haven't you?
But this is so Oracle 7, as you are in 12c have a look at row_limiting_clause and these examples.

[Updated on: Thu, 13 August 2015 04:17]

Report message to a moderator

Re: rownum and order by [message #641392 is a reply to message #641313] Fri, 14 August 2015 02:35 Go to previous messageGo to next message
mats-larsson@iname.com
Messages: 13
Registered: September 2013
Location: Stockholm
Junior Member
Thanks for the tip.
Are there any disadvanteges using my method or is it just too "7ish". Smile
Re: rownum and order by [message #641394 is a reply to message #641392] Fri, 14 August 2015 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The disadvantage is that Oracle will optimize better and better the new way in its patches and versions when the old one will stay.

Re: rownum and order by [message #641414 is a reply to message #641313] Fri, 14 August 2015 07:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Thu, 13 August 2015 05:16

But this is so Oracle 7, as you are in 12c have a look at row_limiting_clause and these examples.


Just don't overrate it. All it does is saving some keystrokes:

SQL> var c clob
SQL> exec dbms_utility.expand_sql_text('SELECT employee_id, last_name FROM hr.employees ORDER BY emp
loyee_id OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY',:c);

PL/SQL procedure successfully completed.

SQL> set long 10000
SQL> print c

C
--------------------------------------------------------------------------------
SELECT "A1"."EMPLOYEE_ID" "EMPLOYEE_ID","A1"."LAST_NAME" "LAST_NAME" FROM  (SELE
CT "A2"."EMPLOYEE_ID" "EMPLOYEE_ID","A2"."LAST_NAME" "LAST_NAME","A2"."EMPLOYEE_
ID" "rowlimit_$_0",ROW_NUMBER() OVER ( ORDER BY "A2"."EMPLOYEE_ID") "rowlimit_$$
_rownumber" FROM "HR"."EMPLOYEES" "A2") "A1" WHERE "A1"."rowlimit_$$_rownumber"<
=CASE  WHEN (5>=0) THEN FLOOR(TO_NUMBER(5)) ELSE 0 END +5 AND "A1"."rowlimit_$$_
rownumber">5 ORDER BY "A1"."rowlimit_$_0"

SQL> 


Or in more readable format:

SELECT  "A1"."EMPLOYEE_ID" "EMPLOYEE_ID",
        "A1"."LAST_NAME" "LAST_NAME"
  FROM  (
         SELECT  "A2"."EMPLOYEE_ID" "EMPLOYEE_ID",
                 "A2"."LAST_NAME" "LAST_NAME",
                 "A2"."EMPLOYEE_ID" "rowlimit_$_0",
                 ROW_NUMBER() OVER ( ORDER BY "A2"."EMPLOYEE_ID") "rowlimit_$$_rownumber"
           FROM  "HR"."EMPLOYEES" "A2"
        ) "A1"
  WHERE "A1"."rowlimit_$$_rownumber"<= CASE
                                         WHEN (5>=0) THEN FLOOR(TO_NUMBER(5))
                                         ELSE 0
                                       END +5
    AND "A1"."rowlimit_$$_rownumber">5
  ORDER BY "A1"."rowlimit_$_0"


SY.
Re: rownum and order by [message #641423 is a reply to message #641414] Fri, 14 August 2015 10:24 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Just don't overrate it.


I do not, I know for the moment this is just the same but I also think that in the future there could be improvements (just like for the ANSI outer join syntax) when the old way to do it (and I admit I still use it) will most likely stay.

Previous Topic: Execute stored procedure after interval
Next Topic: LEFT & Right functions to carve out data from a field
Goto Forum:
  


Current Time: Thu Apr 25 22:54:35 CDT 2024