Home » SQL & PL/SQL » SQL & PL/SQL » sort order based on multiple conditions (10g)
sort order based on multiple conditions [message #355533] Fri, 24 October 2008 14:35 Go to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I almost never ask a question. It must be the hallucinogenic drugs that were put in my pizza because I am having the darndest time solving this one.

I need to have the output paired with a "header" record identified by RECTYPE=H followed by its corresponding "detail" record identified by RECTYPE=D. The sort must be on the GL_ACCOUNT of the header record, ignoring the GL_ACCOUNT of the detail record. Header and detail are connected by the PAYNO column.
create table test999 (payno number(9),
gl_account varchar2(10),
amount number(11,2),
rectype varchar2(1))
/
insert into test999 values (100,'ABC',6000,'H')
/
insert into test999 values (103,'BCD',9000,'H')
/
insert into test999 values (102,'CDE',1000,'H')
/
insert into test999 values (101,'GHI',3000,'H')
/
insert into test999 values (100,'-ABC',null,'D')
/
insert into test999 values (101,'-GHI',null,'D')
/
insert into test999 values (102,'-CDE',null,'D')
/
insert into test999 values (103,'-BCD',null,'D')
/


Expected output:
     PAYNO GL_ACCOUNT             AMOUNT R
---------- ------------------ ---------- -
       100 ABC                      6000 H
       100 -ABC                          D
       103 BCD                      9000 H
       103 -BCD                          D
       102 CDE                      1000 H
       102 -CDE                          D
       101 GHI                      3000 H
       101 -GHI                          D
Re: sort order based on multiple conditions [message #355534 is a reply to message #355533] Fri, 24 October 2008 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data as (
  3      select payno, gl_account, amount, rectype,
  4             case rectype
  5               when 'H' then gl_account
  6               when 'D' then lag(gl_account) over (partition by payno order by rectype desc)
  7             end ord
  8      from test999
  9    )
 10  select payno, gl_account, amount, rectype
 11  from data
 12  order by ord, rectype desc
 13  /
     PAYNO GL_ACCOUNT     AMOUNT R
---------- ---------- ---------- -
       100 ABC              6000 H
       100 -ABC                  D
       103 BCD              9000 H
       103 -BCD                  D
       102 CDE              1000 H
       102 -CDE                  D
       101 GHI              3000 H
       101 -GHI                  D

8 rows selected.

Regards
Michel

[Updated on: Fri, 24 October 2008 14:49]

Report message to a moderator

Re: sort order based on multiple conditions [message #355536 is a reply to message #355534] Fri, 24 October 2008 15:53 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Thanks Michel. I knew it was going to be you to help me on this.
Re: sort order based on multiple conditions [message #355537 is a reply to message #355533] Fri, 24 October 2008 16:46 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select * from test999
  2  order by regexp_substr(gl_account, '[^-]')
  3     , rectype desc;

     PAYNO GL_ACCOUNT     AMOUNT R
---------- ---------- ---------- -
       100 ABC              6000 H
       100 -ABC                  D
       103 BCD              9000 H
       103 -BCD                  D
       102 CDE              1000 H
       102 -CDE                  D
       101 GHI              3000 H
       101 -GHI                  D

8 rows selected.

or simply,
SQL> select * from test999
  2  order by replace(gl_account, '-')
  3     , rectype desc;

     PAYNO GL_ACCOUNT     AMOUNT R
---------- ---------- ---------- -
       100 ABC              6000 H
       100 -ABC                  D
       103 BCD              9000 H
       103 -BCD                  D
       102 CDE              1000 H
       102 -CDE                  D
       101 GHI              3000 H
       101 -GHI                  D

8 rows selected.

[Updated on: Fri, 24 October 2008 16:54]

Report message to a moderator

Re: sort order based on multiple conditions [message #355544 is a reply to message #355537] Sat, 25 October 2008 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Brian,

These work for this example but the requirement is:
Quote:
ignoring the GL_ACCOUNT of the detail record

Now, there are of course better than mine if gl_account of header and detail rows are always in this relation.

Regards
Michel
Re: sort order based on multiple conditions [message #355564 is a reply to message #355544] Sat, 25 October 2008 06:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
select * 
from test999
connect by payno = prior payno
and rectype = 'D' and prior rectype = 'H'
start with rectype='H'

Not because it's better, just because it's interesting.

Ross Leishman
Re: sort order based on multiple conditions [message #355578 is a reply to message #355536] Sat, 25 October 2008 11:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The following eliminates the sub-query:

SCOTT@orcl_11g> SELECT payno,
  2  	    gl_account,
  3  	    amount,
  4  	    rectype
  5  FROM   test999 t
  6  ORDER  BY DECODE
  7  		 (rectype,
  8  		  'H', gl_account,
  9  		  'D', LAG (gl_account) OVER (PARTITION BY payno ORDER BY rectype DESC)),
 10  	       rectype DESC
 11  /

     PAYNO GL_ACCOUNT     AMOUNT R
---------- ---------- ---------- -
       100 ABC              6000 H
       100 -ABC                  D
       103 BCD              9000 H
       103 -BCD                  D
       102 CDE              1000 H
       102 -CDE                  D
       101 GHI              3000 H
       101 -GHI                  D

8 rows selected.

SCOTT@orcl_11g> 


Re: sort order based on multiple conditions [message #355767 is a reply to message #355578] Mon, 27 October 2008 08:27 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, both examples work out marvelously when there is onle one set of records per GL_ACCOUNT. Upon further testing, I see that there can be more than one record per GL_ACOCUNT, in which case the query no longer gives desired results.

Add these two records
insert into test999 values (104,'ABC',1500,'H');
insert into test999 values (104,'none',null,'D');

and the output becomes
     PAYNO GL_ACCOUNT             AMOUNT R
---------- ------------------ ---------- -
       100 ABC                      6000 H
       104 ABC                      1500 H
       100 -ABC                          D
       104 none                          D
       103 BCD                      9000 H
       103 -BCD                          D
       102 CDE                      1000 H
       102 -CDE                          D
       101 GHI                      3000 H
       101 -GHI                          D


I knew there was a reason I was having a tough time with it, even after the hallucinogens wore off.
Re: sort order based on multiple conditions [message #355778 is a reply to message #355767] Mon, 27 October 2008 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand which output do you the want.
Is this:
     PAYNO GL_ACCOUNT             AMOUNT R
---------- ------------------ ---------- -
       100 ABC                      6000 H
       104 ABC                      1500 H
       100 -ABC                          D
       104 none                          D

or
     PAYNO GL_ACCOUNT             AMOUNT R
---------- ------------------ ---------- -
       100 ABC                      6000 H
       100 -ABC                          D
       104 ABC                      1500 H
       104 none                          D

Regards
Michel
Re: sort order based on multiple conditions [message #355781 is a reply to message #355778] Mon, 27 October 2008 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select payno, gl_account, amount, rectype
  2  from test999
  3  order by case rectype
  4             when 'H' then gl_account
  5             when 'D' then lag(gl_account) over (partition by payno order by rectype desc)
  6           end,
  7           rectype desc
  8  /
     PAYNO GL_ACCOUNT     AMOUNT R
---------- ---------- ---------- -
       100 ABC              6000 H
       104 ABC              1500 H
       100 -ABC                  D
       104 none                  D
       103 BCD              9000 H
       103 -BCD                  D
       102 CDE              1000 H
       102 -CDE                  D
       101 GHI              3000 H
       101 -GHI                  D

10 rows selected.

SQL> select payno, gl_account, amount, rectype
  2  from test999
  3  order by case rectype
  4             when 'H' then gl_account
  5             when 'D' then lag(gl_account) over (partition by payno order by rectype desc)
  6           end,
  7           payno,
  8           rectype desc
  9  /
     PAYNO GL_ACCOUNT     AMOUNT R
---------- ---------- ---------- -
       100 ABC              6000 H
       100 -ABC                  D
       104 ABC              1500 H
       104 none                  D
       103 BCD              9000 H
       103 -BCD                  D
       102 CDE              1000 H
       102 -CDE                  D
       101 GHI              3000 H
       101 -GHI                  D

10 rows selected.

Regards
Michel
Re: sort order based on multiple conditions [message #355783 is a reply to message #355778] Mon, 27 October 2008 09:05 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Oh, sorry, I meant that when I add another set of records for the GL_ACCOUNT, the output would become what I showed in the last message, which is incorrect.

However, you have remedied that with your latest query. Thanks Michel.
Previous Topic: problem with one up number
Next Topic: Need help with a query
Goto Forum:
  


Current Time: Sat Dec 10 09:09:59 CST 2016

Total time taken to generate the page: 0.08954 seconds