Home » SQL & PL/SQL » SQL & PL/SQL » order by in sql statement (oracle 9i)
order by in sql statement [message #279751] Sat, 10 November 2007 02:27 Go to next message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
hi every one

i want to order the data not according to any column of the table but
i want to order data depends upon the value of the column
can anyone help me in this regard.
your early action will be highly appreciated.

Thanks in anticipation.
Re: order by in sql statement [message #279753 is a reply to message #279751] Sat, 10 November 2007 02:35 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:

your early action will be highly appreciated.

Supply sample data (in the form of create table and insert scripts) along with expected results(properly formatted using code tags) And makes sure the you have read the Forum guide

Re: order by in sql statement [message #279758 is a reply to message #279751] Sat, 10 November 2007 03:13 Go to previous messageGo to next message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
In the scott's schema
there is a dept table Dept(deptno,dname,loc)
with following values

DEPTNO DNAME LOC
------- -------------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

but i want to display the data order by deptno
not ascending or descending but according to the value of deptno
but i want a custome order like

DEPTNO DNAME LOC
30
40
20
10

i hope you will get my point.
Re: order by in sql statement [message #279761 is a reply to message #279758] Sat, 10 November 2007 03:30 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use the DECODE in ORDER BY:
SQL> SELECT deptno, dname, loc
  2  FROM DEPT
  3  ORDER BY DECODE(deptno, 30, 1, 40, 2, 20, 3, 10, 4);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        20 RESEARCH       DALLAS
        10 ACCOUNTING     NEW YORK
Re: order by in sql statement [message #279765 is a reply to message #279758] Sat, 10 November 2007 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use decode:
SCOTT> select deptno, dname, loc from dept
  2  order by decode(deptno, 30,1, 40,2, 20,3, 10,4, deptno)
  3  /
    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        20 RESEARCH       DALLAS
        10 ACCOUNTING     NEW YORK

4 rows selected.

Or if the algorithm is too complex, you can create your own order function:
SCOTT> create or replace function my_order (p_in in number) return number
  2  is
  3    l_order pls_integer;
  4  begin
  5    case p_in
  6    when 30 then l_order := 1;
  7    when 40 then l_order := 2;
  8    when 20 then l_order := 3;
  9    when 10 then l_order := 4;
 10    else l_order := 5;
 11    end case;
 12    return l_order;
 13  end;
 14  /

Function created.

SCOTT> select deptno, dname, loc from dept order by my_order (deptno);
    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        20 RESEARCH       DALLAS
        10 ACCOUNTING     NEW YORK

4 rows selected.

Regards
Michel
Re: order by in sql statement [message #279769 is a reply to message #279751] Sat, 10 November 2007 03:39 Go to previous message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
Bundle of thanks to all of you.
Previous Topic: PL/SQL Recursion??
Next Topic: DUP_VAL_ON_INDEX (Which Index ?)
Goto Forum:
  


Current Time: Wed Dec 07 12:29:39 CST 2016

Total time taken to generate the page: 0.13683 seconds