Home » SQL & PL/SQL » SQL & PL/SQL » order by relation 'father' , 'mother'
order by relation 'father' , 'mother' [message #355988] Tue, 28 October 2008 22:36 Go to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
i have a table called RELATIONS it has a field RELATIONSHIP with values 'sister' , 'brother' 'father' , 'mother'.
i want the data to appear in the following order
first
'Mother'
'father;
'sister'
how can i achieve this in Order by , or if by any other method
regards
Imtiaz
Re: order by relation 'father' , 'mother' [message #355991 is a reply to message #355988] Tue, 28 October 2008 23:06 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sispk6,

Suppose I have values
'ABC'
'DEF'
'GHI'

and I want to display them in the following order:
'GHI'
'ABC'
'DEF'

SQL> set feedback on;
SQL> ed
Wrote file afiedt.buf

  1  WITH TEMP_TAB AS
  2  (SELECT 'ABC' Col_1 FROM DUAL
  3  UNION
  4  SELECT 'DEF' FROM DUAL
  5  UNION
  6  SELECT 'GHI' FROM DUAL
  7  )
  8  SELECT Col_1 FROM TEMP_TAB
  9* ORDER BY Col_1
 10  ;

COL
---
ABC
DEF
GHI

3 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  WITH TEMP_TAB AS
  2  (SELECT 'ABC' Col_1 FROM DUAL
  3  UNION
  4  SELECT 'DEF' FROM DUAL
  5  UNION
  6  SELECT 'GHI' FROM DUAL
  7  )
  8  SELECT 
  9  DECODE(COL_1, 'ABC', 2, 'DEF', 3, 'GHI', 1, 999)
 10  SORT_1, COL_1 FROM TEMP_TAB
 11* ORDER BY SORT_1
 12  ;

    SORT_1 COL
---------- ---
         1 GHI
         2 ABC
         3 DEF

3 rows selected.



Hope this helps,

Regards,
Jo

[Updated on: Tue, 28 October 2008 23:13]

Report message to a moderator

Re: order by relation 'father' , 'mother' [message #356018 is a reply to message #355991] Wed, 29 October 2008 01:19 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Or, alternatively, move the DECODE function directly into the ORDER BY clause:
SQL> WITH TEMP_TAB AS
  2      (SELECT 'ABC' Col_1 FROM DUAL
  3      UNION
  4      SELECT 'DEF' FROM DUAL
  5      UNION
  6      SELECT 'GHI' FROM DUAL
  7      )
  8      SELECT COL_1 FROM TEMP_TAB
  9    ORDER BY DECODE(COL_1, 'ABC', 2, 'DEF', 3, 'GHI', 1, 999);

COL
---
GHI
ABC
DEF

SQL>
Re: order by relation 'father' , 'mother' [message #356029 is a reply to message #355988] Wed, 29 October 2008 01:53 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hai,

You will also try as by create the RELATIONSHIP table like.

Create table Relationship (Slno       Number(2),
                           Relation   Varchar2(20));


insert the Required Relationship. Then join that table with this table to sort the data.


Regards,

CVS.
Re: order by relation 'father' , 'mother' [message #356032 is a reply to message #356029] Wed, 29 October 2008 02:05 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@cvs_1984,
cvs_1984 wrote on Wed, 29 October 2008 12:23
Hai,

You will also try as by create the RELATIONSHIP table like.

Create table Relationship (Slno       Number(2),
                           Relation   Varchar2(20));


insert the Required Relationship. Then join that table with this table to sort the data.



I didn't quite understand your point here. Can you please explain how will a mere join work without using an ORDER BY Clause alone? And above all why join to a new table? http://img2.mysmiley.net/imgs/smile/confused/confused0075.gif

Regards,
Jo
Previous Topic: How to optimize this code?
Next Topic: Paging to show limited number of records
Goto Forum:
  


Current Time: Fri Dec 02 20:31:03 CST 2016

Total time taken to generate the page: 0.11050 seconds