Need Help in Order By.,

[Shining Star]

Hi All -

i have a table TEST111 which have the following data..,

SQL> SELECT * FROM TEST11
  2  /

POSNEG
----------
RR1
RR2
RR3-
RR1-
RR2-
RR3
RR3+
RR2+
RR1+

i want the output to be displayed as

RR1-
RR1
RR1+
RR2-
RR2
RR2+
RR3-
RR
RR3+

Could any one help?

Thanks

[PAVB]

select  posneg
from    test11
order   by substr(posneg,1,3),decode(substr(posneg,4,1),'-','1','+','3','2')
;

[Shining Star]

Thanks PAVB.

That worked like wat i want.

Thanks..,

[Shining Star]

hi PAVB -

Just a small change.

if i have RR10,RR10-,RR10+, i am getting the output as follows,

SQL> SELECT   POSNEG
  2  FROM     TEST11
  3  ORDER BY SUBSTR(POSNEG,1,3),
  4           DECODE(SUBSTR(POSNEG,4,1),'-','1',
  5                                     '+','3',
  6                                     '2');

POSNEG                                                                          
----------                                                                      
RR1-                                                                            
RR1                                                                             
RR10-                                                                           
RR10                                                                            
RR10+                                                                           
RR1+                                                                            
RR2-                                                                            
RR2                                                                             
RR2+                                                                            
RR3-                                                                            
RR3                                                                                                                                                  
RR3+                                                                            

12 rows selected.

But, i want RR10,RR10-,RR10+ to be displayed after RR3+.

Could you help?

Thanks

[PAVB]

You already got your free service on this one and you blew it up because of wrong/incomplete specifications.

Now is your turn, you do something and hopefully somebody will review it.

[jay_l_a]

How's this for a wordy solution?

SELECT posneg
FROM test11
ORDER BY LPAD(SUBSTR(posneg,3,LENGTH(REPLACE(REPLACE(posneg, '-', NULL), '+', NULL))-2), 5),
DECODE(SUBSTR(posneg,(LENGTH(REPLACE(REPLACE(posneg, '-', NULL), '+', NULL)))+1,1),'-','1',
'+','3',
'2');