Home » SQL & PL/SQL » SQL & PL/SQL » Order Ascending or Descending based on condition (Oracle 10g)
Order Ascending or Descending based on condition [message #404394] Thu, 21 May 2009 11:13 Go to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi All,

I have a requirement that I need to order a table in ascending order or descending order based on a condition.

Here is the test case:
create table pras_account(accountno varchar2(40), asc_order_boo varchar2(1), bill_id number(9));

create table pras_bill(bill_id number(9), item varchar2(40), amount number(9));

insert into pras_account values('Acc1','T',1);
insert into pras_account values('Acc2','F',2);

insert into pras_bill values(1,'Table',2000);
insert into pras_bill values(1,'Chair',1000);
insert into pras_bill values(1,'Bed',5000);
insert into pras_bill values(1,'Cup Board',3000);
insert into pras_bill values(2,'Table',2000);
insert into pras_bill values(2,'Chair',1000);
insert into pras_bill values(2,'Bed',5000);
insert into pras_bill values(2,'Cup Board',3000);


In this testdata, I wanted to order the column PRAS_BILL.AMOUNT based on the value in column PRAS_ACCOUNT.ASC_ORDER_BOO;
If the value of ASC_ORDER_BOO is 'T' then I want the results in ascending order on column AMOUNT. If the value is 'F' then I want the results in descending order on column AMOUNT.

I wrote the sql query in the following manner:

select accountno, item, amount from
    (select a.accountno, b.item, b.amount from pras_account a, pras_bill b 
     where a.accountno='Acc1' and a.bill_id = b.bill_id 
     order by b.amount) a,
    (select '1' from pras_account where accountno='Acc1' and asc_order_boo='T' and rownum=1)b
union
select accountno, item, amount from
    (select a.accountno, b.item, b.amount from pras_account a, pras_bill b 
     where a.accountno='Acc1' and a.bill_id = b.bill_id 
     order by b.amount desc) a,
    (select '1' from pras_account where accountno='Acc1' and asc_order_boo='F' and rownum=1)b;


Its giving me result as
ACCOUNTNO  ITEM         AMOUNT
Acc1	   Bed	        5000
Acc1	   Chair	1000
Acc1	   Cup Board	3000
Acc1	   Table	2000


But its not ordering by the column AMOUNT as I am expecting.

However the individual query is giving me the expected result:
select accountno, item, amount from
    (select a.accountno, b.item, b.amount from pras_account a, pras_bill b 
     where a.accountno='Acc1' and a.bill_id = b.bill_id 
     order by b.amount) a,
    (select '1' from pras_account where accountno='Acc1' and asc_order_boo='T' and rownum=1)b


Result is
ACCOUNTNO  ITEM         AMOUNT
Acc1	   Chair	1000
Acc1	   Table	2000
Acc1	   Cup Board	3000
Acc1	   Bed	        5000


Why it is not working with union clause?

Also, if anyone has any better idea to achieve that ordering condition requirement then please let me know.

Thanks in advance.
prashas_d
Re: Order Ascending or Descending based on condition [message #404405 is a reply to message #404394] Thu, 21 May 2009 12:51 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You have 2 separate order by clauses, one for each query. you need one single order by clause at the end. You'll have to wrap the query to make it an inline view. Also, replace union with union all.
Re: Order Ascending or Descending based on condition [message #404562 is a reply to message #404394] Fri, 22 May 2009 05:00 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
"Union" does an explicit ordering ..So once you do an order by there after do an union ,it(union) rearranges the rows .
This can be avoided by use of "UNION ALL" in the query.
SQL> ed
Wrote file afiedt.buf

  1  select accountno, item, amount from
  2      (select a.accountno, b.item, b.amount from pras_account a, pras_bill b
  3       where a.accountno='Acc1' and a.bill_id = b.bill_id
  4       order by b.amount) a,
  5      (select '1' from pras_account where accountno='Acc1' and asc_order_boo='T' and rownum=1)b
  6  union all
  7  select accountno, item, amount from
  8      (select a.accountno, b.item, b.amount from pras_account a, pras_bill b
  9       where a.accountno='Acc1' and a.bill_id = b.bill_id
 10       order by b.amount desc) a,
 11*     (select '1' from pras_account where accountno='Acc1' and asc_order_boo='F' and rownum=1)b
SQL> /

ACCOUNTNO                                ITEM                                         AMOUNT
---------------------------------------- ---------------------------------------- ----------
Acc1                                     Chair                                          1000
Acc1                                     Table                                          2000
Acc1                                     Cup Board                                      3000
Acc1                                     Bed                                            5000
Re: Order Ascending or Descending based on condition [message #404564 is a reply to message #404562] Fri, 22 May 2009 05:13 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
ayush_anand wrote on Fri, 22 May 2009 11:00
"Union" does an explicit ordering

Since when?

Re: Order Ascending or Descending based on condition [message #404566 is a reply to message #404562] Fri, 22 May 2009 05:14 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member

Thanks for the responses!!

So, I can meet the requirement by using UNION ALL clause.

But is there any other way of writing the query for meeting my requirement? Actually with the current logic, I have to write the same query twice accompanied by two more queries. So totally it is causing me 4 queries to get executed Sad

prashas_d
Re: Order Ascending or Descending based on condition [message #404571 is a reply to message #404394] Fri, 22 May 2009 05:42 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
On a closer inspection I don't believe you need any variety of union. Though the extra queries you mention in your last post may change that, without meaningful details I can't tell.
The original query is way overcomplicated because you don't know how to use case in an order by.

This should sort it:
SELECT a.accountno, b.item, b.amount 
FROM pras_account a, pras_bill b
WHERE a.accountno = 'Acc1' and a.bill_id = b.bill_id 
ORDER BY (CASE WHEN asc_order_boo = 'T' THEN b.amount ELSE b.amount * -1 END);
Re: Order Ascending or Descending based on condition [message #404576 is a reply to message #404566] Fri, 22 May 2009 05:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think your query can be rewritten as:
select a.accountno
      ,b.item
      ,b.amount 
from   pras_account a
      ,pras_bill b 
where  a.accountno='Acc1' 
and    a.bill_id = b.bill_id
and exists (select 1 
            from   pras_account c
            where  accountno = a.accountno
            and    asc_order_boo in ('T','F'));
Re: Order Ascending or Descending based on condition [message #404578 is a reply to message #404571] Fri, 22 May 2009 06:17 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
@cookiemonster,

yeah, this is the better one. Moreover, the idea of multiplying by -1 for performing the descending order is a good one. Smile

I didn't know that we can use case expression in the order statement and now I realised Razz

If we can use case expression, then I tried with decode as well and giving me the same results.

@JRowBottom,

Sorry, the query that you have provided is not performing the ordering that I am expecting.

prashas_d
Re: Order Ascending or Descending based on condition [message #404590 is a reply to message #404578] Fri, 22 May 2009 07:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I wasn't addressing the Ordering problem - if you check the post numbers, you'll see I was replying to the post where you asked how to rewrite the query to avoid using multiple UNIONS.

Cookiemonster has already shown how to use a case statement in an Order by
Re: Order Ascending or Descending based on condition [message #404596 is a reply to message #404590] Fri, 22 May 2009 07:30 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
okok.. got it.

Sorry for my previous message.
Re: Order Ascending or Descending based on condition [message #404671 is a reply to message #404564] Fri, 22 May 2009 13:47 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
http://download.oracle.com/docs/cd/B10501_01/em.920/a86647/vmqtune.htm

Search for this

The difference between the UNION and UNION ALL is that UNION requires a sort operation to eliminate any rows that are duplicated across the two row sets, while UNION ALL returns all rows, even if they are duplicated. If duplicated rows are not important, using UNION ALL can avoid potentially expensive sorts, merges, and filtering operations.
Previous Topic: WebServices
Next Topic: Optimistic locking
Goto Forum:
  


Current Time: Tue Dec 06 12:10:02 CST 2016

Total time taken to generate the page: 0.16001 seconds