Home » SQL & PL/SQL » SQL & PL/SQL » I need to sort the record omiting negative sign. (sql*plus)
I need to sort the record omiting negative sign. [message #386843] Tue, 17 February 2009 02:44 Go to next message
good
Messages: 27
Registered: July 2000
Junior Member
Hi,

I am new to this site, I need you help to solve my problem. I am using a query to fetch a value from table for example :-

There are 5 records with the value 40,30,14,10,-35 :-

Select mark from exam order by mark desc;


Now the output it shows like this :-

40
30
14
10
-35

But i need to omit the negative sign and sort the value to show the value like :

40
-35
30
14
10

If any one has a solution, please let me know. thanks in advance.
Re: I need to sort the record omiting negative sign. [message #386846 is a reply to message #386843] Tue, 17 February 2009 02:48 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
So you want to ORDER the records BY the ABSolute value in a DESCending order? Very Happy

MHE
Re: I need to sort the record omiting negative sign. [message #386856 is a reply to message #386846] Tue, 17 February 2009 03:14 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
Thanks. It works for single select statement, but i do have a UNION ALL to combine 2 queries. it gives a error message.

what to do?
Re: I need to sort the record omiting negative sign. [message #386858 is a reply to message #386843] Tue, 17 February 2009 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ABS (just to precise Maaher's answer).

Regards
Michel

[Updated on: Tue, 17 February 2009 03:18]

Report message to a moderator

Re: I need to sort the record omiting negative sign. [message #386861 is a reply to message #386856] Tue, 17 February 2009 03:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
'an error message'? What is that? Any specific error? And where does this 'union all' come from. That's new information.

Anyway, you could do this:
with yourtable as 
(
  select  40 mark from dual union all
  select  14 mark from dual union all
  select  30 mark from dual union all
  select -35 mark from dual union all
  select  10 mark from dual  
),
yourothertable as 
(
  select -40 a_column from dual union all
  select -14 a_column from dual union all
  select -15 a_column from dual union all
  select   0 a_column from dual union all
  select   2 a_column from dual  
)

select mark col1
from   yourtable
union all 
select a_column col1
from   yourothertable
order by abs(col1)
/
MHE
Re: I need to sort the record omiting negative sign. [message #386863 is a reply to message #386856] Tue, 17 February 2009 03:20 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
good wrote on Tue, 17 February 2009 10:14
Thanks. It works for single select statement, but i do have a UNION ALL to combine 2 queries. it gives a error message.


As we don't have access to your system(s) and some of use aren't psychic we need more information.
good wrote on Tue, 17 February 2009 10:14

what to do?


Maybe post the query and the errormessage ?

EDIT: Maaher is one of the psychic's around here and posted what could be a solution Wink

[Updated on: Tue, 17 February 2009 03:21]

Report message to a moderator

Re: I need to sort the record omiting negative sign. [message #386865 is a reply to message #386843] Tue, 17 February 2009 03:22 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
This is my orginal query, tried with ABS functio to it.

SELECT   TRIM (holding_type) holding_type, as_of_date, detail_value,
         holding_type_name
    FROM gws_issue_compositions_v comp
   WHERE instr_id = '82        '
     AND holding_type NOT IN
            ('AVERAGE COUPON', 'BOND QUALITY', 'CREDIT QUALITY',
             'COUPON RATE BREAKDOWN', 'MATURITY BREAKDOWN')
     AND as_of_date =
            (SELECT MAX (as_of_date)
               FROM gws_issue_compositions_v comp_max
              WHERE comp_max.instr_id = comp.instr_id
                AND comp_max.ctyp_sys_no = comp.ctyp_sys_no
                AND TRUNC (as_of_date) <= TO_DATE ('2009-02-28', 'yyyy-mm-dd'))
     AND holding_type IN ('CURRENCY DISTRIBUTION')
UNION ALL
SELECT   TRIM (holding_type) holding_type, as_of_date, detail_value,
         holding_type_name
    FROM gws_issue_compositions_v comp
   WHERE instr_id = '82        '
     AND holding_type IN ('AVERAGE COUPON')
     AND as_of_date =
            (SELECT MAX (as_of_date)
               FROM gws_issue_compositions_v comp_max
              WHERE comp_max.instr_id = comp.instr_id
                AND comp_max.ctyp_sys_no = comp.ctyp_sys_no
                AND as_of_date <= TO_DATE ('2009-02-28', 'yyyy-mm-dd'))
ORDER BY 1, abs(detail_value) DESC, 4
Re: I need to sort the record omiting negative sign. [message #386866 is a reply to message #386843] Tue, 17 February 2009 03:23 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
It give following error, while executing it ...

ORA-01785: ORDER BY item must be the number of a SELECT-list expression
Re: I need to sort the record omiting negative sign. [message #386870 is a reply to message #386863] Tue, 17 February 2009 03:26 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
MarcS wrote on Tue, 17 February 2009 10:20
EDIT: Maaher is one of the psychic's around here and posted what could be a solution Wink
Laughing Comes with the job, I guess...

MHE
Re: I need to sort the record omiting negative sign. [message #386871 is a reply to message #386866] Tue, 17 February 2009 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the error message is self-explanatory.

Regards
Michel
Re: I need to sort the record omiting negative sign. [message #386876 is a reply to message #386871] Tue, 17 February 2009 03:31 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Tue, 17 February 2009 10:27
And the error message is self-explanatory.

Regards
Michel



Yes and ... no, in this case.

I notice the mix of position and column-name the ORDER BY, which should work.

I count 4 attributes in the SELECT-list, so the ORDER BY on 4 should work.

So I'm overlooking something, or the information provided isn't complete


Re: I need to sort the record omiting negative sign. [message #386879 is a reply to message #386871] Tue, 17 February 2009 03:34 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
Michel Cadot wrote on Tue, 17 February 2009 03:27
And the error message is self-explanatory.

Regards
Michel




what you are coming to say ? I couldn't able to get it ?

thanks.
Re: I need to sort the record omiting negative sign. [message #386880 is a reply to message #386876] Tue, 17 February 2009 03:37 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
MarcS wrote on Tue, 17 February 2009 03:31
Michel Cadot wrote on Tue, 17 February 2009 10:27
And the error message is self-explanatory.

Regards
Michel



Yes and ... no, in this case.

I notice the mix of position and column-name the ORDER BY, which should work.

I count 4 attributes in the SELECT-list, so the ORDER BY on 4 should work.

So I'm overlooking something, or the information provided isn't complete






I have tried by change it like below :-

ORDER BY holding_type, abs(detail_value) DESC, holding_type_name

Re: I need to sort the record omiting negative sign. [message #386881 is a reply to message #386880] Tue, 17 February 2009 03:39 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
good wrote on Tue, 17 February 2009 10:37

I have tried by change it like below :-

ORDER BY holding_type, abs(detail_value) DESC, holding_type_name




And does that work?
Re: I need to sort the record omiting negative sign. [message #386883 is a reply to message #386880] Tue, 17 February 2009 03:41 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Move the ABS() to the select list as a new column. That should do the trick.


MHE
Re: I need to sort the record omiting negative sign. [message #386884 is a reply to message #386843] Tue, 17 February 2009 03:44 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Seems oracle can cope with a mix of numeric and named notation for an order by so long as the named column isn't piped through a function:

SQL> SELECT ROWNUM a, ROWNUM b, ROWNUM c
  2  FROM dual
  3  UNION
  4  SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
  5  FROM dual
  6  ORDER BY 1, b, 3;

         A          B          C
---------- ---------- ----------
         1          1          1
         2          1          1

SQL> SELECT ROWNUM a, ROWNUM b, ROWNUM c
  2  FROM dual
  3  UNION
  4  SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
  5  FROM dual
  6  ORDER BY 1, abs(b), 3;
ORDER BY 1, abs(b), 3
            *
ERROR at line 6:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression



OP - try specifying all the columns in the ORDER BY by name.


EDIT: spent far too long knocking up that test case obviously.

[Updated on: Tue, 17 February 2009 03:46]

Report message to a moderator

Re: I need to sort the record omiting negative sign. [message #386886 is a reply to message #386881] Tue, 17 February 2009 03:45 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
MarcS wrote on Tue, 17 February 2009 03:39
good wrote on Tue, 17 February 2009 10:37

I have tried by change it like below :-

ORDER BY holding_type, abs(detail_value) DESC, holding_type_name




And does that work?




No, Still the same error ?
Re: I need to sort the record omiting negative sign. [message #386887 is a reply to message #386883] Tue, 17 February 2009 03:47 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Maaher wrote on Tue, 17 February 2009 10:41
Move the ABS() to the select list as a new column. That should do the trick.
MHE


I guess Maaher is spot-on.

The error isn't that self-explanatory in this case Wink

EDIT: Needlessy to say that I've learned something I didn't know before Smile



[Updated on: Tue, 17 February 2009 03:48]

Report message to a moderator

Re: I need to sort the record omiting negative sign. [message #386888 is a reply to message #386883] Tue, 17 February 2009 03:47 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
Maaher wrote on Tue, 17 February 2009 03:41
Move the ABS() to the select list as a new column. That should do the trick.


MHE



i need to solve with out adding new display column.
Re: I need to sort the record omiting negative sign. [message #386890 is a reply to message #386843] Tue, 17 February 2009 03:48 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Here was me thinking that would work.
Making the union SELECT an inline view does work:

SQL> SELECT * FROM(
  2  SELECT ROWNUM a, ROWNUM b, ROWNUM c
  3  FROM dual
  4  UNION
  5  SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
  6  FROM dual)
  7  ORDER BY a, abs(b), c;

         A          B          C
---------- ---------- ----------
         1          1          1
         2          1          1

SQL> 
Re: I need to sort the record omiting negative sign. [message #386891 is a reply to message #386884] Tue, 17 February 2009 03:49 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
cookiemonster wrote on Tue, 17 February 2009 03:44
Seems oracle can cope with a mix of numeric and named notation for an order by so long as the named column isn't piped through a function:

SQL> SELECT ROWNUM a, ROWNUM b, ROWNUM c
  2  FROM dual
  3  UNION
  4  SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
  5  FROM dual
  6  ORDER BY 1, b, 3;

         A          B          C
---------- ---------- ----------
         1          1          1
         2          1          1

SQL> SELECT ROWNUM a, ROWNUM b, ROWNUM c
  2  FROM dual
  3  UNION
  4  SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
  5  FROM dual
  6  ORDER BY 1, abs(b), 3;
ORDER BY 1, abs(b), 3
            *
ERROR at line 6:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression



OP - try specifying all the columns in the ORDER BY by name.


EDIT: spent far too long knocking up that test case obviously.


i have tried you thought also, still the same problem .

ORDER BY holding_type, abs(detail_value) DESC, holding_type_name

Re: I need to sort the record omiting negative sign. [message #386892 is a reply to message #386890] Tue, 17 February 2009 03:52 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
cookiemonster wrote on Tue, 17 February 2009 03:48
Here was me thinking that would work.
Making the union SELECT an inline view does work:

SQL> SELECT * FROM(
  2  SELECT ROWNUM a, ROWNUM b, ROWNUM c
  3  FROM dual
  4  UNION
  5  SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
  6  FROM dual)
  7  ORDER BY a, abs(b), c;

         A          B          C
---------- ---------- ----------
         1          1          1
         2          1          1

SQL> 



Tried you logic also, same error :

SELECT   TRIM (holding_type) a, as_of_date b, detail_value c,
         holding_type_name d
    FROM gws_issue_compositions_v comp
   WHERE instr_id = '82        '
     AND holding_type NOT IN
            ('AVERAGE COUPON', 'BOND QUALITY', 'CREDIT QUALITY',
             'COUPON RATE BREAKDOWN', 'MATURITY BREAKDOWN')
     AND as_of_date =
            (SELECT MAX (as_of_date)
               FROM gws_issue_compositions_v comp_max
              WHERE comp_max.instr_id = comp.instr_id
                AND comp_max.ctyp_sys_no = comp.ctyp_sys_no
                AND TRUNC (as_of_date) <= TO_DATE ('2009-02-28', 'yyyy-mm-dd'))
     AND holding_type IN ('CURRENCY DISTRIBUTION')
UNION ALL
SELECT   TRIM (holding_type) a, as_of_date b, detail_value c,
         holding_type_name d
    FROM gws_issue_compositions_v comp
   WHERE instr_id = '82        '
     AND holding_type IN ('AVERAGE COUPON')
     AND as_of_date =
            (SELECT MAX (as_of_date)
               FROM gws_issue_compositions_v comp_max
              WHERE comp_max.instr_id = comp.instr_id
                AND comp_max.ctyp_sys_no = comp.ctyp_sys_no
                AND as_of_date <= TO_DATE ('2009-02-28', 'yyyy-mm-dd'))
ORDER BY a, abs(c) DESC, d

Re: I need to sort the record omiting negative sign. [message #386893 is a reply to message #386890] Tue, 17 February 2009 03:53 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
cookiemonster wrote on Tue, 17 February 2009 03:48
Here was me thinking that would work.
Making the union SELECT an inline view does work:

SQL> SELECT * FROM(
  2  SELECT ROWNUM a, ROWNUM b, ROWNUM c
  3  FROM dual
  4  UNION
  5  SELECT ROWNUM + 1 a, ROWNUM b, ROWNUM c
  6  FROM dual)
  7  ORDER BY a, abs(b), c;

         A          B          C
---------- ---------- ----------
         1          1          1
         2          1          1

SQL> 



Thanks It works... GREAT !!!!
Re: I need to sort the record omiting negative sign. [message #386896 is a reply to message #386888] Tue, 17 February 2009 03:59 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
good wrote on Tue, 17 February 2009 10:47
Maaher wrote on Tue, 17 February 2009 03:41
Move the ABS() to the select list as a new column. That should do the trick.


MHE



i need to solve with out adding new display column.
I should have been more clear. You can wrap the select (with union all) in an inner select. Ok, I'll set up a small test case:
WITH table_1 AS
(
  SELECT 1 a, -2 b, 3 c FROM dual UNION ALL
  SELECT 2 a,  2 b, 2 c FROM dual UNION ALL
  SELECT 1 a, -1 b, 1 c FROM dual UNION ALL
  SELECT 1 a,  0 b, 0 c FROM dual
),
     table_2 AS
(
  SELECT 2 a, -5 b, 0 c FROM dual UNION ALL
  SELECT 1 a, 12 b, 1 c FROM dual UNION ALL
  SELECT 2 a,  7 b, 2 c FROM dual UNION ALL
  SELECT 2 a, -1 b, 3 c FROM dual UNION ALL
  SELECT 1 a,  3 b, 4 c FROM dual UNION ALL
  SELECT 3 a,  0 b, 3 c FROM dual UNION ALL
  SELECT 1 a, -2 b, 2 c FROM dual UNION ALL
  SELECT 2 a,  8 b, 0 c FROM dual
)
SELECT a
     , b
     , c
-- small trick: put brackets around the select
FROM
(     
  SELECT a, b, c
  FROM table_1
  UNION ALL
  SELECT a, b, c
  FROM table_2
)
ORDER BY 1, abs(b), 3
/

MHE

Edit: Damn, too late. Sad

[Updated on: Tue, 17 February 2009 04:00]

Report message to a moderator

Previous Topic: anonymous block
Next Topic: ETL in Oracle 10g (merged 4)
Goto Forum:
  


Current Time: Fri Dec 09 19:22:15 CST 2016

Total time taken to generate the page: 0.05889 seconds