Oracle-L: RE: Brain cramp on analytical functions and grouping.

From: Aponte, Tony <AponteT_at_hsn.net>
Date: Mon, 12 Aug 2002 13:43:28 -0800
Message-ID: <F001.004B2361.20020812134328@fatcity.com>

I pivoted the result set on the WO column. This example works for up to 12 distinct values for the CP column. I don't know if you need to pivot it again to get back to the original result set but at least it gives you the sort order you described.

HTH
Tony Aponte
Home Shopping Network, Inc.

create table work_orders (WO VARCHAR2(7),CP VARCHAR2(7))

insert into work_orders values ('W859674','A120003')

insert into work_orders values ('W859674','A120004')

insert into work_orders values ('W859674','A120006')

insert into work_orders values ('W838796','A120000')

insert into work_orders values ('W838796','A120003')

insert into work_orders values ('W844656','A120000')

insert into work_orders values ('W844656','A120004')

insert into work_orders values ('W849769','A120000')

insert into work_orders values ('W849769','A120004')

insert into work_orders values ('W858835','A120000')

insert into work_orders values ('W858835','A120003')

insert into work_orders values ('W880717','A120003')

insert into work_orders values ('W880717','A120006')

commit

SELECT
g1

,MAX(DECODE(line_no,01,value,NULL)) A, 
MAX(DECODE(line_no,02,value,NULL)) B, 
MAX(DECODE(line_no,03,value,NULL)) C, 
MAX(DECODE(line_no,04,value,NULL)) D, 
MAX(DECODE(line_no,05,value,NULL)) E, 
MAX(DECODE(line_no,06,value,NULL)) F, 
MAX(DECODE(line_no,07,value,NULL)) G, 
MAX(DECODE(line_no,08,value,NULL)) H, 
MAX(DECODE(line_no,09,value,NULL)) I, 
MAX(DECODE(line_no,10,value,NULL)) J, 
MAX(DECODE(line_no,11,value,NULL)) K, 
MAX(DECODE(line_no,12,value,NULL)) L
FROM (SELECT g1,value,row_number() over(partition by g1 order by g1 nulls last) line_no

FROM (SELECT wo g1,cp value from work_orders) )
GROUP BY g1
ORDER BY 2,3,4,5,6,7,8,9,10,11,12,13,1 G1 A B C D E F G H I J K L

W838796 A120000 A120003          
W858835 A120000 A120003          
W844656 A120000 A120004          
W849769 A120000 A120004          
W859674 A120003 A120004 A120006         
W880717 A120003 A120006

-----Original Message-----
mailto:Rich.Jesse_at_qtiworld.com]
Sent: Wednesday, August 07, 2002 2:35 PM To: Multiple recipients of list ORACLE-L

OK, my brain hurts. A dev wants a query to return in a peculiar sort order on 8.1.7.2, but I'm having no luck. He needs groups of rows sorted by the whole of their key values. That doesn't sound right, so maybe an example:

Table A

RI      WO          CP      RC      RN
1       W859674           A120003             3     1
2       W859674           A120004             3     2
3       W859674           A120006             3     3

4       W838796           A120000             2     1
5       W838796           A120003             2     2

6       W844656           A120000             2     1
7       W844656           A120004             2     2

8       W849769           A120000             2     1
9       W849769           A120004             2     2

10      W858835             A120000           2   1
11      W858835             A120003           2   2

12      W880717             A120003           2   1
13      W880717             A120006           2   2

In an attempt to breakdown the problem, I added columns RC and RN as "COUNT(*) OVER (PARTITION BY WO)" and "ROW_NUMBER() OVER (PARTITION BY WO ORDER BY CP)", respectively. I also added the row spacing here for clarity.

The dev would like the group of WO W858835, rows 10 and 11, immediately after WO group W838796 because the groups have the same number of rows (RC) and same values of CP within the groups.

MIN and MAX would work in this case, but if the groups are larger than two it's no guarantee of order. What I was thinking is a report column that would be the concatonation of all the CPs for the group, but since it's VARCHAR2 and not numeric, I'm not sure how that could be accomplished.

Any suggestions, including favorite beers, is more than welcome.

TIA!

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

--
Please see the official ORACLE-L FAQ:  <http://www.orafaq.com> http://www.orafaq.com
--
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Aponte, Tony
  INET: AponteT_at_hsn.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Mon Aug 12 2002 - 16:43:28 CDT