Order by in oracle

[helpme]

Hi,

I have this table A as

Cust_id cust_2_id flag
1 1 new
2 2 new
3 3 new
4 4 new
1 1 skip
1 1 skip
2 2 skip
4 4 skip

I want to order by such that...for each cust, the record with new flag appears first..then skip..then next record...as below

Cust_id cust_2_id flag
1 1 new
1 1 skip
1 1 skip
2 2 new
2 2 skip
3 3 new
4 4 new
4 4 skip

Can anyone help me with this?

Thanks

[helpme]

1 more thing..

No alalytical fucntion...as i would be using this in pl/sql...version < 9

[davey23uk]

you can use analytical functions with an execute immedite if you want

[jmodic]

So what is so special about this ordering? Based on your description, the following will do just fine:

.... ORDER BY Cust_id, cust_2_id, flag;

[tamilselvan]

Modic,

I think "helpme" might have thought if some other value is stored in the flag column - other than new and skip, then what could be the result.

May be he needs to add decode in the order by clause.

SQL> desc v1
 Name              Null?    Type
 ----------------- -------- --------------------
 ID1                        NUMBER(38)
 ID2                        NUMBER(38)
 TXT                        VARCHAR2(10)

SQL> select * from v1 order by id1, id2, txt ;

       ID1        ID2 TXT
---------- ---------- ----------
         1          1 new
         1          1 skip
         1          1 skip
         1          1 skip
         2          2 new
         2          2 skip
         2          2 skip

7 rows selected.

The above code works as long as the TXT 
column has only 2 values.

SQL> insert into v1 values (1,1,'abc');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from v1
     order by id1, id2, txt ;

       ID1        ID2 TXT
---------- ---------- ----------
         1          1 abc     -----------> watch
         1          1 new
         1          1 skip
         1          1 skip
         1          1 skip
         2          2 new
         2          2 skip
         2          2 skip

8 rows selected.

SQL>  select * from v1
      order by id1, id2, decode(txt,'new',1,2) ;

       ID1        ID2 TXT
---------- ---------- ----------
         1          1 new
         1          1 skip
         1          1 skip
         1          1 skip
         1          1 abc    <--------- watch
         2          2 new
         2          2 skip
         2          2 skip

8 rows selected.

Tamil

[helpme]

jmodic...actually yes.....i THOUGHT it wont work...so tried all other stuff.....:-&..

[robertbalmer]

The closest I could get to your desired result :

SELECT V.ID1,V.ID2,V.TXT
FROM V1 V
GROUP BY ROLLUP (V.ID1,V.ID2,V.TXT)

or just

SELECT V.ID1,V.ID2,V.TXT
FROM V1 V
GROUP BY (V.ID1,V.ID2,V.TXT)

rollup result:
1 1 NEW
1 1 SKIP
1 1
1
2 2 NEW
2 2 SKIP
2 2
2
3 3 NEW
3 3
3
4 4 NEW
4 4 SKIP
4 4
4