rownum and order by [message #641308] |
Thu, 13 August 2015 03:50 |
|
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 #641414 is a reply to message #641313] |
Fri, 14 August 2015 07:07 |
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 |
|
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.
|
|
|