Re: An Interesting Grouping Question | One Solution

You got it.

Jared

On Friday 31 August 2001 15:40, MacGregor, Ian A. wrote:
> One will definitely need a separator so one can tell course 111 from course
> concat(course 1, course 11). I should have indicated that. If you are
> asking why you used a colon specifically, I don't know.
>
> Ian MacGregor
> Stanford Linear Acclerator Center
> ian_at_slac.stanford.edu
>
> -----Original Message-----
> Sent: Friday, August 31, 2001 1:08 PM
> To: ORACLE-L_at_fatcity.com
> Cc: MacGregor, Ian A.
>
>
>
> Ian,
>
> You just had to throw down the gauntlet, didn't you? :)
>
> Here's my solution. You may consider it cheating, as I've done pretty much
> what your
> developer came up with, but I used a stored funtion.
>
> This can probably be done in 'pure' SQL, but I don't have enough time to
> ponder it right now.
>
> Incidentally, I concatenate a ':' in the string with every course id?
> Guess why? ;)
>
> Jared
>
> ---- create the test table
>
> drop table ian;
>
> create table ian ( emp_id number(2), course_id number(2));
>
>
> insert into ian values( 1, 1 );
> insert into ian values( 2, 2 );
> insert into ian values( 2, 3 );
> insert into ian values( 3, 3 );
> insert into ian values( 3, 4 );
> insert into ian values( 3, 5 );
> insert into ian values( 4, 3 );
> insert into ian values( 4, 4 );
> insert into ian values( 4, 5 );
> insert into ian values( 5, 2 );
> insert into ian values( 5, 3 );
> insert into ian values( 6, 1 );
> insert into ian values( 7, 2 );
> insert into ian values( 8, 3 );
> insert into ian values( 8, 4 );
>
> commit;
>
>
> --- create the function
>
> create or replace function get_course_set( emp_id_in ian.emp_id%type )
> return varchar2
> is
> vCourseStr varchar2(100);
> begin
>
> for emprec in (
> select course_id
> from ian
> where emp_id = emp_id_in
> ) loop
> vCourseStr := vCourseStr || to_char(emprec.course_id) || ':' ;
> end loop;
>
> return vCourseStr;
>
> end;
> /
>
> --- run the test
>
> col course_set format a30 head 'COURSE SET'
>
> break on course_set skip 1
>
> select
> get_course_set(emp_id) course_set
> , emp_id
> from ian
> group by emp_id
> order by 1
> /
>
>
>
>
>
>
> "MacGregor,
> Ian A." To: Multiple recipients of
> list ORACLE-L <ORACLE-L_at_fatcity.com> <ian_at_SLAC.Stan cc:
> ford.EDU> Subject: RE: An Interesting
> Grouping Question | One Solution Sent by:
> root_at_fatcity.c
> om
>
>
> 08/31/01 11:00
> AM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
> I could not think of a way to do it with any of the analytical functions.
> The developer's method was to make an array with
> one element being the emplid and the other a string with all that employees
> courses sorted and concatenated together. Then one can group the
> employees by comparing the strings.
>
> There are many superb SQL programmers on the list. I thought one might
> take a stab at it.
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_slac.stanford.edu
>
> -----Original Message-----
> Sent: Thursday, August 30, 2001 2:47 PM
> To: Multiple recipients of list ORACLE-L
>
> "MacGregor, Ian A." wrote:
> > Given the following Table
> >
> > emplid course_id
> > ------ ---------
> > 1 1
> > 2 2
> > 2 3
> > 3 3
> > 3 4
> > 3 5
> > 4 3
> > 4 4
> > 4 5
> > 5 2
> > 5 3
> > 6 1
> > 7 2
> > 8 3
> > 8 4
>
> ---------------------------------------------------------------------------
>-----------------
>
> > What statement would you write to group employees by the set of courses
>
> they have taken. In otherwords each employee in a group must have taken
> the same as the others in the group, not one class more nor less. In this
> example the employees making up the groups would be
>
> > 1,6
> > 2,5
> > 3,4
> > 7
> > and 8
> >
> > I had this posed by one of my developers. He had also come up with a
>
> solution which didn't take a relational approach. The approach is not
> exotic, and I suspect it will be proposed by many people. He'd like a
> relational one.
>
> > Ian MacGregor
> > Stanford Linear Accelerator Center
> > ian_at_slac.stanford.edu
>
> 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
> --------------------------------------------------------------
> http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
> --------------------------------------------------------------
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Stephane Faroult
> INET: sfaroult_at_oriole.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: MacGregor, Ian A.
> INET: ian_at_SLAC.Stanford.EDU
>
> 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: Jared Still
  INET: jkstill_at_cybcon.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).