| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: An Interesting Grouping Question | One Solution
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
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 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;
/
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 asolution 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
from (select course_id
from courses_taken
where emplid = p_emplid
order by 1 desc) b) a
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: INET: Jared.Still_at_radisys.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 Fri Aug 31 2001 - 15:21:58 CDT
 |
 |