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  |
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 #404562 is a reply to message #404394] |
Fri, 22 May 2009 05:00   |
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 #404571 is a reply to message #404394] |
Fri, 22 May 2009 05:42   |
cookiemonster
Messages: 13965 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   |
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 #404590 is a reply to message #404578] |
Fri, 22 May 2009 07:04   |
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 #404671 is a reply to message #404564] |
Fri, 22 May 2009 13:47  |
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.
|
|
|
Goto Forum:
Current Time: Sat Feb 15 08:32:44 CST 2025
|