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

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 07 Aug 2002 16:38:40 -0800
Message-ID: <F001.004AE564.20020807163840@fatcity.com>

(answer follows the original question)

> -----Original Message-----
> From: Jesse, Rich [mailto:Rich.Jesse_at_qtiworld.com]
>
> 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.

You could always write a function to return all values of RC for a particular WO, but I hope that someone would come up with a better way. (see example)

SQL> describe my_data ;

 Nom                   NULL ?   Type
 --------------------- -------- ------------------
 C                              CHAR(1)
 N                              NUMBER(1)

SQL> select * from my_data ;
C         N
- ---------
A         1
A         2
B         1
B         2
B         3
C         1
C         2
D         1
D         3

9 ligne(s) sélectionnée(s).

SQL> create function get_all_n (c_in in char) return varchar2 2 is

  3     all_n varchar2 (4000) ;
  4     cursor c_all_n (key_in varchar2) is
  5       select n from my_data where c = key_in ;
  6  begin
  7     all_n := null ;
  8     for c_all_n_rec in c_all_n (c_in)
  9     loop
 10        all_n := all_n || to_char (c_all_n_rec.n, 'FM0000000000') ;
 11     end loop ;
 12     return all_n ;

13 end get_all_n ;
14 /
Fonction créée.

SQL> column all_n format a40
SQL> select c, get_all_n (c) as all_n
2 from my_data ;
C ALL_N

- ----------------------------------------

A 00000000010000000002
A 00000000010000000002
B 000000000100000000020000000003
B 000000000100000000020000000003
B 000000000100000000020000000003
C 00000000010000000002
C 00000000010000000002
D 00000000010000000003
D 00000000010000000003
9 ligne(s) sélectionnée(s).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.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).

Received on Wed Aug 07 2002 - 19:38:40 CDT