CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-order-by
Meqenaneri Vacharq
Meqenaneri Vacharq

Reputation: 99

oracle loop ordering

I have loop script by pl/sql, my script returned order by i asc,

DECLARE 
    i NUMBER;
BEGIN
    FOR rec IN (
        SELECT 1 as i from dual
        UNION
        SELECT 2 as i from dual
        UNION
        SELECT 3 as i from dual
        UNION
        SELECT 4 as i from dual
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE (rec.i); 
    END LOOP; 
end;

and it returned 

1
2
3
4

but I need ordering

1
4
3
2

what function should I use to solve the problem, thank you advence

Upvotes: 0

Views: 2370

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

As Main starting reference point of interest for indexing may be i-1 in the order by part of the sql statement.

So, the following cursor may be prefered :

SELECT * FROM
(
 SELECT 1 as i from dual
 UNION
 SELECT 2 as i from dual
 UNION
 SELECT 3 as i from dual
 UNION
 SELECT 4 as i from dual
)
ORDER BY sign(i-1), (i-1) desc;

SQL Fiddle Demo

Upvotes: 0

SwapnaSubham Das
SwapnaSubham Das

Reputation: 537

Instead of using UNION operator use UNION ALL operator because UNION operator return sorted distinct result. On the other hand UNION ALL operator return duplicate unsorted result

try the below query:- SET SERVEROUTPUT ON; DECLARE i NUMBER; BEGIN FOR rec IN ( SELECT 1 as i from dual UNION ALL SELECT 4 as i from dual UNION ALL SELECT 3 as i from dual UNION ALL SELECT 2 as i from dual ) LOOP DBMS_OUTPUT.PUT_LINE (rec.i); END LOOP; end; /

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would expect this to return what you want:

DECLARE 
    i NUMBER;
BEGIN
    FOR rec IN (
        SELECT 1 as i from dual
        UNION ALL
        SELECT 4 as i from dual
        UNION ALL
        SELECT 3 as i from dual
        UNION ALL
        SELECT 2 as i from dual
     ) LOOP
        DBMS_OUTPUT.PUT_LINE (rec.i); 
    END LOOP; 
end;

However, that is just because the data is small. Oracle doesn't guarantee the ordering of result sets without an ORDER BY. So a safer method is:

DECLARE 
    i NUMBER;
BEGIN
    FOR rec IN (
        SELECT i
        FROM (SELECT 1 as i, 1 as ord from dual UNION ALL
              SELECT 4 as i, 2 from dual UNION ALL
              SELECT 3 as i, 3 from dual UNION ALL
              SELECT 2 as i, 4 from dual
             ) x
        ORDER BY ord
     ) LOOP
        DBMS_OUTPUT.PUT_LINE (rec.i); 
    END LOOP; 
end;

Upvotes: 1

Related Questions