RE: Brain cramp on analytical functions and grouping.

[Aponte, Tony]

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-----
From: Jesse, Rich [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 07, 2002 2:35 PM
To: Multiple recipients of list ORACLE-L
Subject: Brain cramp on analytical functions and grouping.


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
[EMAIL PROTECTED]              Quad/Tech International, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).