Display a single employee from each department. The employee displayed should be a random selection and not just the first one retrieved from the table.
(extra columns added for explanatory purposes)
SQL> select e1.deptno,
2 e1.empno,
3 e1.dept_rank,
4 e2.max_emps,
5 s1.rand_num,
6 mod(s1.rand_num, e2.max_emps)+1 filter_condition
7 from (select deptno,
8 empno,
9 rank() over (partition by deptno order by empno) as dept_rank
10 from emp) e1,
11 (select deptno,
12 count(deptno) max_emps
13 from emp
14 group by deptno) e2,
15 (select to_char(sysdate, 'ss') rand_num
16 from dual) s1
17 where e1.deptno is not null
18 and e1.deptno = e2.deptno
19 and e1.dept_rank = mod(s1.rand_num, e2.max_emps)+1;
DEPTNO EMPNO DEPT_RANK MAX_EMPS RA FILTER_CONDITION
---------- ---------- ---------- ---------- -- ----------------
10 7934 3 3 26 3
20 7566 2 5 26 2
30 7654 3 6 26 3-- Daniel W. Fink http://www.optimaldba.com
VIVEK_SHARMA wrote:
Where are they advantageous to use & where not ?
Thanks
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
