Home » SQL & PL/SQL » SQL & PL/SQL » Order By Clause (Oracle 9i)
Order By Clause [message #384027] Sun, 01 February 2009 22:27 Go to next message
saleharafi
Messages: 2
Registered: February 2009
Location: Hyderabad
Junior Member
Hi All,

I have not worked on SQL extensively and needed help with order by clause in Oracle PL/SQL.

I have the following table data

ColA ColB
10001 Suspect
10001 Bad
10001 Good
10001 Pattern
10011 Suspect
10011 Good
10011 Bad
10011 Pattern


I want to sort on ColA and ColB. ColA can be sorted Desc or Asc but when it comes to ColB I want it to be sorted in an order that gives the following result.

ColA ColB
10001 Pattern
10001 Bad
10001 Good
10001 Suspect
10011 Pattern
10011 Bad
10011 Good
10011 Suspect

Basically the ColB is ordered not in Asc or Desc by in an order I desire. First the word Pattern, then Bad, Good and Suspect.

Any help appreciated.

Thanks
Devi
Re: Order By Clause [message #384031 is a reply to message #384027] Sun, 01 February 2009 22:42 Go to previous messageGo to next message
st33chen
Messages: 11
Registered: January 2009
Junior Member
hi,

have you tried

order by colA, case
when colB='Pattern' then 1
when colB='Suspect' then 2
when colB='Good' then 3
when colB='Bad' then 4
end

or

order by colA, instr('Pattern, Suspect, Good, Bad', colB)

Re: Order By Clause [message #384033 is a reply to message #384027] Sun, 01 February 2009 22:51 Go to previous message
saleharafi
Messages: 2
Registered: February 2009
Location: Hyderabad
Junior Member
Cool that works... Thank you soo much.
I like this forum got answers quickly with some good experts. I will soo improve my PL/SQL skills this way.
Previous Topic: CONNECT BY inner workings
Next Topic: How to solved ORA-04068 error (2 threads merged by bb)
Goto Forum:
  


Current Time: Mon Dec 05 15:21:58 CST 2016

Total time taken to generate the page: 0.15917 seconds