Home » SQL & PL/SQL » SQL & PL/SQL » ORDER BY in a particular order
ORDER BY in a particular order [message #560899] Tue, 17 July 2012 13:33 Go to next message
rkhatiwala
Messages: 142
Registered: April 2007
Senior Member
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

Hi,
I have data like:-
event_id	iss_nbr
171350	        2012051WR
171350	        2012041WR
171350          2011081CC
171350	        2012041WA
171350	        2012031WW
171350          2011081WW
171350          2011081CR
171350          2011081CA


The possible last two characters of the iss_nbr can be:-
WW, WR,WA,CR,CA,CC

And I want it to be ORDER BY as follows.
WR
WA
CR
CA
WW
CC

So for example, in above case, it should be
event_id	iss_nbr
171350	        2012051WR
171350	        2012041WR
171350	        2012041WA
171350	        2012031WW
171350          2011081CR
171350          2011081CA
171350          2011081WW
171350          2011081CC


How can I do it while loading the table?

Thanks,
RK
Re: ORDER BY in a particular order [message #560900 is a reply to message #560899] Tue, 17 July 2012 13:35 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>How can I do it while loading the table?

ORDER BY is valid on SELECT; not DML.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: ORDER BY in a particular order [message #560905 is a reply to message #560899] Tue, 17 July 2012 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As BlackSwan said ORDER BY onloading is meaningless as a table is a heap and so, in the end, does not contain any order.

On SELECT, you can use the following ORDER BY clause:
ORDER BY decode(substr(iss_nbr, -2), 'WR',1, 'WA',2, 'CR',3, 'CA',4, 'WW',5, 'CC',6, 7), iss_nbr

Regards
Michel
Re: ORDER BY in a particular order [message #560909 is a reply to message #560905] Tue, 17 July 2012 14:14 Go to previous messageGo to next message
rkhatiwala
Messages: 142
Registered: April 2007
Senior Member
Thanks Michel,

But I've already tried that. Also tried putting iss_nbr, before DECODE.... but it doesn't order by the 1st 6 digits..
select event_id, iss_nbr from wc_iss_dtls
where event_id in (171350)--,171351,171352,171353,171354,171355,171356) 
order by event_id, iss_nbr,
decode(substr(iss_nbr, -2), 'WR',1, 'WA',2, 'CR',3, 'CA',4, 'WW',5, 'CC',6, 7)

result is:- 
171350	2012041WA
171350	2012041WR
171350	2012051CR
171350	2012051WW



It should be
171350	2012051CR
171350	2012051WW
171350	2012041WR
171350	2012041WA
Re: ORDER BY in a particular order [message #560918 is a reply to message #560909] Tue, 17 July 2012 14:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
select event_id, iss_nbr from wc_iss_dtls
where event_id in (171350)--,171351,171352,171353,171354,171355,171356) 
order by event_id, substr(iss_nbr,1,length(iss_nbr) - 2) DESC,
decode(substr(iss_nbr, -2), 'WR',1, 'WA',2, 'CR',3, 'CA',4, 'WW',5, 'CC',6, 7)
/


SY.

[Updated on: Tue, 17 July 2012 15:12]

Report message to a moderator

Re: ORDER BY in a particular order [message #560921 is a reply to message #560918] Tue, 17 July 2012 15:10 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Make up your mind!
rkhatiwala
I want it to be ORDER BY as follows.
WR
WA
CR
CA
WW
CC

(After Michel replied)rkhatiwala
It should be
171350 2012051CR
171350 2012051WW
171350 2012041WR
171350 2012041WA

You want    : WR, WA, CR, CA, ..
It should be: CR, WW, WR, WA, ...

Which one of these is true?
Re: ORDER BY in a particular order [message #560922 is a reply to message #560921] Tue, 17 July 2012 15:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
Issue with OP's solution was you need to order not by whole iss_nbr but rather by iss_nbr without last two characters and in descending order:

with wc_iss_dtls as (
                     select 171350 event_id,'2012051WR' iss_nbr from dual union all
                     select 171350,'2012041WR' from dual union all
                     select 171350,'2012041WA' from dual union all
                     select 171350,'2012031WW' from dual union all
                     select 171350,'2011081CR' from dual union all
                     select 171350,'2011081CA' from dual union all
                     select 171350,'2011081WW' from dual union all
                     select 171350,'2011081CC' from dual
                    )
-- end of on-the-fly data sample
select  event_id,
        iss_nbr,substr(iss_nbr, -2) x
 from   wc_iss_dtls
  where event_id in (171350)--,171351,171352,171353,171354,171355,171356)
  order by event_id,
        substr(iss_nbr,1,length(iss_nbr) - 2) DESC,
        decode(
               substr(iss_nbr, -2),
               'WR',1,
               'WA',2,
               'CR',3,
               'CA',4,
               'WW',5,
               'CC',6,
                    7
              )
/

  EVENT_ID ISS_NBR   X
---------- --------- --
    171350 2012051WR WR
    171350 2012041WR WR
    171350 2012041WA WA
    171350 2012031WW WW
    171350 2011081CR CR
    171350 2011081CA CA
    171350 2011081WW WW
    171350 2011081CC CC

8 rows selected.

with wc_iss_dtls as (
                     select 171350 event_id,'2012051CR' iss_nbr from dual union all
                     select 171350,'2012051WW' from dual union all
                     select 171350,'2012041WR' from dual union all
                     select 171350,'2012041WA' from dual
                    )
-- end of on-the-fly data sample
select  event_id,
        iss_nbr,substr(iss_nbr, -2) x
 from   wc_iss_dtls
  where event_id in (171350)--,171351,171352,171353,171354,171355,171356)
  order by event_id,
        substr(iss_nbr,1,length(iss_nbr) - 2) DESC,
        decode(
               substr(iss_nbr, -2),
               'WR',1,
               'WA',2,
               'CR',3,
               'CA',4,
               'WW',5,
               'CC',6,
                    7
              )
/

  EVENT_ID ISS_NBR   X
---------- --------- --
    171350 2012051CR CR
    171350 2012051WW WW
    171350 2012041WR WR
    171350 2012041WA WA

SQL>  


SY.

[Updated on: Tue, 17 July 2012 15:18]

Report message to a moderator

Re: ORDER BY in a particular order [message #560926 is a reply to message #560921] Tue, 17 July 2012 15:50 Go to previous messageGo to next message
rkhatiwala
Messages: 142
Registered: April 2007
Senior Member
It has to consider the first six digits first, because it is the YYYYMM ( YEAR-MONTH). so 201205 has to come first, then 201204 and so on. After considering this, if there are two entries for same YYYYMM, then go for the last two characters, which are WR,WA,WW, CR,CA,CC..
Re: ORDER BY in a particular order [message #560927 is a reply to message #560926] Tue, 17 July 2012 16:06 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You should have said that in your FIRST message.
Re: ORDER BY in a particular order [message #560928 is a reply to message #560927] Tue, 17 July 2012 16:42 Go to previous messageGo to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
Hi ,

You Said , once the first 6 characters YYYYMM are same , then it should pick up the below order fro last two characters

WR,WA,WW,CR,CA,CC..

Also,
you expect the date in below order :--

******EXPECTED Result in Initial Query***
event_id iss_ibr
171350 2012051WR
171350 2012041WR
171350 2012041WA
171350 2012031WW
171350 2011081CR
171350 2011081CA
171350 2011081WW
171350 2011081CC

In the expected results if you see , the last four records, as per your latest requirement it should be

event_id iss_ibr
171350 2011081WW
171350 2011081CR
171350 2011081CA
171350 2011081CC

Also based on your initial requirement , below is the query which might help you..

select event_id,
iss_nbr
from wc_iss_dtls
order by substr(iss_nbr,1,length(iss_nbr) - 2) desc,
decode(substr(iss_nbr, -2),
'WR',1,
'WA',2,
'CR',3,
'CA',4,
'WW',5,
'CC',6,
7)

Hope this helps.

Sandeep.
Re: ORDER BY in a particular order [message #560939 is a reply to message #560926] Tue, 17 July 2012 21:55 Go to previous messageGo to next message
Paulie
Messages: 22
Registered: May 2012
Location: Ireland
Junior Member
rkhatiwala wrote on Tue, 17 July 2012 21:50
It has to consider the first six digits first, because it is the YYYYMM ( YEAR-MONTH). so 201205 has to come first, then 201204 and so on. After considering this, if there are two entries for same YYYYMM, then go for the last two characters, which are WR,WA,WW, CR,CA,CC..


It's unfortunate that you didn't say this at the beginning - people
are trying to help.

  order by event_id,
        substr(iss_nbr, 1, 6) DESC,     <=== add to Solomon's code
        substr(iss_nbr,1,length(iss_nbr) - 2) DESC,
        decode(


That seems to do the trick.

HTH,

Paul...

[Updated on: Tue, 17 July 2012 21:56]

Report message to a moderator

Re: ORDER BY in a particular order [message #561048 is a reply to message #560939] Wed, 18 July 2012 08:51 Go to previous message
rkhatiwala
Messages: 142
Registered: April 2007
Senior Member
sorry for not mentioning that earlier, can't believe i missed that part.
Previous Topic: Reading Flat File with Header (2 Merged)
Next Topic: Easy query
Goto Forum:
  


Current Time: Thu Oct 02 11:48:36 CDT 2014

Total time taken to generate the page: 0.12976 seconds