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  |
joy_division
Messages: 4963
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  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #355537 is a reply to message #355533] |
Fri, 24 October 2008 16:46 |
 |
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 #355578 is a reply to message #355536] |
Sat, 25 October 2008 11:33 |
 |
Barbara Boehmer
Messages: 9100
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 |
joy_division
Messages: 4963
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 #355781 is a reply to message #355778] |
Mon, 27 October 2008 09:02 |
|
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
joy_division
Messages: 4963
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.
|
|
|
|
Goto Forum:
Current Time: Sun Dec 08 06:08:36 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
