Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: An Interesting Grouping Question

RE: An Interesting Grouping Question

From: Larry Elkins <>
Date: Sat, 01 Sep 2001 07:02:45 -0700
Message-ID: <>


Stephane mentioned the possibility of using analytical functions. I know that you have used the analytical functions in the past so you may have already taken Stephane's idea and run with it, and maybe Stephane has as well. If not, here is Stephane's solution modified so that the DB function "course_index" (and the call to it) is replaced with an in-line view using the ROW_NUMBER function.

Note in the following select that "course_set", using the in-line view with the ROW_NUMBER function, returns the same results as Stephane's calculation using the DB function. I included the use of Stephane's code calling his function for comparison purposes.

  sum(x.course_id * power(10,z.stephane - 1)) course_set,   sum(x.course_id * power(10,course_index(x.emplid,x.course_id) - 1)) Steph FROM
  ian x,
  (SELECT emplid,

          ROW_NUMBER() OVER (PARTITION BY emplid
                             ORDER BY course_id desc) as stephane
   FROM ian) z
  x.emplid = z.emplid and
  x.course_id = z.course_id

When you showed the groupings, you had emplid's with the same courses on a single line separated by commas. I'm not sure if that is how you want the output, or, if you were simply showing the pairings. If you want the output like that, then obviously the above isn't doing that, and with the number of matching emplid's unknown in the "real" world, a call to a DB function to get them on the same line would be needed.

I've got a question that maybe someone can answer. One approach I tried was using the CAST function. This required creating a type (not a purely relational approach):

create or replace type IanTableType as table of number /

  1 select emplid,
  2 cast(multiset(select course_id from ian x where x.emplid = ian.emplid ) as IanTableType) x
  3 from ian
  4* group by emplid

---------- ------------------------------
         1 IANTABLETYPE(1)
         2 IANTABLETYPE(2, 3)
         3 IANTABLETYPE(3, 4, 5)
         4 IANTABLETYPE(3, 4, 5)
         5 IANTABLETYPE(2, 3)
         6 IANTABLETYPE(1)
         7 IANTABLETYPE(2)
         8 IANTABLETYPE(3, 4)

8 rows selected.

Now this gives me each emplid on a single line with the courses they took. If I could order by the CAST function I could accomplish the same thing as in the prior query. But, you can't order by the CAST function, at least not directly. Any tricks to simulate this or get around it?


Larry G. Elkins

> I don't think you can, unless, perhaps, you use the analytical functions
> which I have never had the opportunity to play with. Problem number 1
> is getting an identifier for each set of courses. Since you must get
> this through a 'GROUP BY', the only chance is a numerical expression. An
> obvious candidate is something like sum(course_id * power(10, n -1))
> where n is the order (starting with 1) of course ids suitably ordered
> for each employee - restarting from 1 with each employee. Getting n is
> the trouble. You cannot get it through rownums and in-line views, it
> would require some kind of ugly three-way correlation between views in
> the FROM clause and a subquery. The best solution I see, but it's not a
> 'pure play' one, is to create a function
> create or replace function course_index(p_emplid in number,
> p_course_id in number)
> return number
> as
> n_val number;
> begin
> select a.n
> into n_val
> from (select b.course_id, rownum n
> from (select course_id
> from courses_taken
> where emplid = p_emplid
> order by 1 desc) b) a
> where a.course_id = p_course_id;
> return n
> exception
> when no_data_found then
> return null;
> end;
> Then it becomes relatively easy to write
> select emplid, sum(course_id * power(10, course_index(emplid,
> course_id) - 1) course_set
> from courses_taken
> group by emplid
> and then to do whatever you want.
> --
> Regards,
> Stephane Faroult
> Oriole Corporation
> Voice: +44 (0) 7050-696-269
> Fax: +44 (0) 7050-696-449
> Performance Tools & Free Scripts

Please see the official ORACLE-L FAQ:
Author: Larry Elkins

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: (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 Sat Sep 01 2001 - 09:02:45 CDT

Original text of this message