# Recent employment Puzzle

For each location, show 2 most recently hired employees

• Use a single SELECT statement only.
• ename1 and hiredate1 columns should correspond the latest hired employee while ename1 and hiredate1 columns – the previous one

Expected Result:

LOC ENAME1 HIREDATE1 ENAME2 HIREDATE2
NEW YORK MILLER 23-JAN-82 KING 17-NOV-81
CHICAGO JAMES 03-DEC-81 MARTIN 28-SEP-81

# Solutions:

### Solution #1. Using Self-Join and MAX functions

```SELECT d.loc,
MAX(e1.ename) KEEP(DENSE_RANK FIRST ORDER BY e1.hiredate DESC) ename1,
MAX(e1.hiredate) hiredate1,
MAX(e2.ename) KEEP(DENSE_RANK FIRST ORDER BY e2.hiredate DESC) ename2,
MAX(e2.hiredate) hiredate2
FROM scott.emp e1 JOIN scott.emp e2 ON e1.deptno=e2.deptno
AND e1.hiredate>=e2.hiredate
AND e1.ROWID!=e2.ROWID
JOIN scott.dept d ON e1.deptno=d.deptno
GROUP BY d.loc;```

### Solution #2. Using LISTAGG and REGEXP_SUBSTR functions

```SELECT d.loc,
MAX(ename) KEEP(DENSE_RANK FIRST ORDER BY hiredate DESC) ename1,
MAX(hiredate) hiredate1,
REGEXP_SUBSTR(LISTAGG(ename, '|') WITHIN GROUP (ORDER BY hiredate DESC),
'[^|]+',1,2) ename2,
REGEXP_SUBSTR(LISTAGG(hiredate, '|') WITHIN GROUP (ORDER BY hiredate DESC),
'[^|]+',1,2) hiredate2
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
GROUP BY d.loc
ORDER BY 1;```

### Solution #3. Using CTE, ROW_NUMBER, and Self-Join

```WITH x AS (
SELECT d.loc, e.ename, e.hiredate,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT a.loc, a.ename ename1, a.hiredate hiredate1,
b.ename ename2, b.hiredate hiredate2
FROM x a JOIN x b ON a.loc=b.loc AND a.rn=1 AND b.rn=2;```

### Solution #4. Using Pivot

```WITH x AS (
SELECT d.loc, e.ename, e.hiredate,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT loc, e1_ename AS ename1, e1_hdate AS hiredate1,
e2_ename AS ename2, e2_hdate AS hiredate2
FROM x
PIVOT (
MAX(ename) ename, MAX(hiredate) hdate FOR rn IN (1 AS e1, 2 AS e2)
)
ORDER BY 1;```

### Solution #5. Simulating Pivot with MAX and DECODE functions

```WITH x AS (
SELECT d.loc, e.ename, e.hiredate,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT loc,
MAX(DECODE(rn,1,ename)) ename1,
MAX(DECODE(rn,1,hiredate)) hiredate1,
MAX(DECODE(rn,2,ename)) ename2,
MAX(DECODE(rn,2,hiredate)) hiredate2
FROM x
GROUP BY loc
ORDER BY 1;```

### Solution #6. Using CONNECT BY

```WITH x AS (
SELECT d.loc, e.ename, e.hiredate,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT loc, PRIOR ename ename1, PRIOR hiredate hiredate1,
ename ename2, hiredate hriedate2
FROM x
WHERE rn=2
CONNECT BY loc=PRIOR loc
AND rn=PRIOR rn+1;```

### Solution #7. Using LEAD and ROW_NUMBER Analytic functions

```WITH x AS (
SELECT d.loc, e.ename ename1, e.hiredate hiredate1,
LEAD(e.ename,1) OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) ename2,
LEAD(e.hiredate,1) OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) hiredate2,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT loc, ename1, hiredate1, ename2, hiredate2
FROM x
WHERE rn=1
ORDER BY 1;```

### Solution #8. Using Model Clause:

```SELECT loc, ename1, hiredate1, ename2, hiredate2
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
MODEL
RETURN UPDATED ROWS
PARTITION BY (d.loc)
DIMENSION BY (
ROW_NUMBER()OVER(PARTITION BY d.loc ORDER BY e.hiredate DESC) AS rn
)
MEASURES(
ename AS ename1, hiredate AS hiredate1,
ename AS ename2, hiredate AS hiredate2
)
RULES(
ename2[1]   =ename1[2],
hiredate2[1]=hiredate1[2]
)
ORDER BY 1;
```

You can execute the above SQL statements in Oracle Live SQL environment.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

# Namesake Puzzle

Show groups of employees having the same last name.

• Use a single SELECT statement only
• Use hr.employees table

# Solutions:

### Solution #1: Using Subquery with HAVING clause:

``````SELECT first_name, last_name, department_id, employee_id
FROM hr.employees
WHERE last_name IN (SELECT last_name
FROM hr.employees
GROUP BY last_name
HAVING COUNT(*)>1)
ORDER BY 2,1``````

### Solution #2: Using Multi-Column Subquery with NO HAVING clause

``````SELECT first_name, last_name, department_id, employee_id
FROM hr.employees
WHERE (last_name, 1) IN (SELECT last_name, SIGN(COUNT(*)-1)
FROM hr.employees
GROUP BY last_name)
ORDER BY 2,1``````

### Solution #3: Using Subquery with IN operator

``````SELECT first_name, last_name, department_id, employee_id
FROM hr.employees a
WHERE last_name IN (SELECT b.last_name
FROM hr.employees b
WHERE a.employee_id!=b.employee_id)
ORDER BY 2,1``````

### Solution #4: Using Self-Join with duplicate elimination in GROUP BY

``````SELECT a.first_name, a.last_name, a.department_id, a.employee_id
FROM hr.employees a JOIN hr.employees b ON a.last_name=b.last_name
AND a.employee_id!=b.employee_id
GROUP BY a.first_name, a.last_name, a.department_id, a.employee_id
ORDER BY 2,1``````

### Solution #5: Using a filter by COUNT analytic function with PARTITION BY

``````WITH x AS (
SELECT first_name, last_name, department_id, employee_id,
COUNT(*) OVER(PARTITION BY last_name) cnt
FROM hr.employees
)
SELECT first_name, last_name, department_id, employee_id
FROM x
WHERE cnt>1
ORDER BY 2,1``````

### Solution #6: Mimicking COUNT analytic function with MODEL clause

(credit to Naoto Katayama)

``````WITH x AS (
SELECT first_name, last_name, department_id, employee_id, cnt
FROM hr.employees
MODEL
RETURN UPDATED ROWS
DIMENSION BY (last_name, employee_id)
MEASURES(first_name, department_id, 0 AS cnt)
RULES (cnt[ANY, ANY]=COUNT(*)[CV(), ANY])
)
SELECT first_name, last_name, department_id, employee_id
FROM x
WHERE cnt>1
ORDER BY 2,1``````

### Solution #7: Filtering by LEAD and LAG analytic functions

``````WITH x AS (
SELECT first_name, last_name, department_id, employee_id,
LAG (last_name,1) OVER(ORDER BY last_name) lag_name,
FROM hr.employees
)
SELECT first_name, last_name, department_id, employee_id
FROM x
ORDER BY 2,1``````

### Solution #8: Using MODEL clause with dummy measure for SIGN over analytic function expression

``````SELECT first_name, last_name, department_id, employee_id
FROM hr.employees
MODEL
RETURN UPDATED ROWS
PARTITION BY (last_name)
DIMENSION BY (SIGN(COUNT(*) OVER(PARTITION BY last_name)-1) AS n,
employee_id)
MEASURES(first_name, department_id, 0 AS dummy)
RULES (dummy[1, ANY]=1)
ORDER BY 2,1``````

### Solution #9: Using UNPIVOT with DISTINCT option over CONNECT BY with PRIOR

``````WITH x AS (
SELECT first_name curr_first, last_name, department_id curr_dept, employee_id curr_id,
PRIOR first_name prior_first, PRIOR department_id prior_dept, PRIOR employee_id prior_id
FROM hr.employees
WHERE level=2
CONNECT BY last_name=PRIOR last_name AND employee_id>PRIOR employee_id
)
SELECT DISTINCT first_name, last_name, department_id, employee_id
FROM x
UNPIVOT(
(first_name, department_id, employee_id)  for dummy IN ((curr_first, curr_dept, curr_id),
(prior_first,prior_dept,prior_id))
)
ORDER BY 2,1;``````

You can execute the above SQL statements in Oracle Live SQL environment.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## Puzzle of the Week #21:

Produce a report that shows employee name, his/her immediate manager name, and the next level manager name. The following conditions should be met:

• Use Single SELECT statement only
• Use mgr column to identify employee’s immediate manager
• The query should work in Oracle 11g.
• A preferred solution should use only a single instance of emp table.

### Expected Result:

```NAME1      NAME2      NAME3
---------- ---------- ------
SMITH      FORD       JONES
ALLEN      BLAKE      KING
WARD       BLAKE      KING
JONES      KING
MARTIN     BLAKE      KING
BLAKE      KING
CLARK      KING
SCOTT      JONES      KING
KING
TURNER     BLAKE      KING
JAMES      BLAKE      KING
FORD       JONES      KING
MILLER     CLARK      KING```

### Solutions:

#1. Using connect_by_root, sys_connect_by_path, and regexp_substr functions

```col name1 for a10
col name2 for a10
col name3 for a10
WITH x AS(
SELECT CONNECT_BY_ROOT(ename) name,
SYS_CONNECT_BY_PATH(ename, ',') path,
CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT name, REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 2) name2,
REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 3) name3
FROM x
GROUP BY name, empno
ORDER BY empno;```

#2. Using CONNECT BY twice

```WITH x AS (
SELECT ename, PRIOR ename mname, empno, mgr
FROM emp
WHERE LEVEL=2 OR mgr IS NULL
CONNECT BY PRIOR empno=mgr
)
SELECT ename name1, mname name2, MAX(PRIOR mname) name3
FROM x
WHERE LEVEL<=2
CONNECT BY PRIOR empno=mgr
GROUP BY ename, mname, empno
ORDER BY empno```

#3. Using CONNECT BY and Self Outer Join

```WITH x AS (
SELECT ename, PRIOR ename mname, PRIOR mgr AS mgr, empno
FROM emp
WHERE LEVEL=2 OR mgr IS NULL
CONNECT BY PRIOR empno=mgr
)
SELECT x.ename name1, x.mname name2, e.ename name3
FROM x LEFT JOIN emp e ON x.mgr=e.empno
ORDER BY x.empno```

#4. Using 2 Self Outer Joins

```SELECT a.ename name1, b.ename name2, c.ename name3
FROM emp a LEFT JOIN emp b ON a.mgr=b.empno
LEFT JOIN emp c ON b.mgr=c.empno
ORDER BY a.empno```

#5. Using CONNECT BY and PIVOT

```SELECT name1, name2, name3
FROM (
SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
PIVOT(
MAX(ename)
FOR lvl IN (1 AS name1, 2 AS name2, 3 AS name3)
)
ORDER BY empno;```

#6. PIVOT Simulation

```WITH x AS (
SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT MAX(DECODE(lvl, 1, ename)) name1,
MAX(DECODE(lvl, 2, ename)) name2,
MAX(DECODE(lvl, 3, ename)) name3
FROM x
GROUP BY empno
ORDER BY empno;```

#7. Using CONNECT BY and no WITH/Subqueries (Credit to Krishna Jamal)

```SELECT ename Name1, PRIOR ename Name2,
DECODE(LEVEL,
3, CONNECT_BY_ROOT(ename),
4, TRIM(BOTH ' ' FROM
REPLACE(
REPLACE(SYS_CONNECT_BY_PATH(PRIOR ename, ' '), PRIOR ename),
CONNECT_BY_ROOT(ename)))
) Name3
FROM emp
CONNECT BY PRIOR empno = mgr
ORDER BY empno;```

#8. A composition of Methods 1 and 7:

```SELECT ename Name1, PRIOR ename Name2,
CASE WHEN LEVEL IN (3,4)
THEN REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ename, ','),'[^,]+',1,LEVEL-2)
END AS Name3
FROM emp
CONNECT BY PRIOR empno = mgr
ORDER BY empno;```

#9. Using NTH_VALUE Analytic function (Oracle 11.2 and up):

```WITH x AS (
SELECT CONNECT_BY_ROOT(ename) n1, CONNECT_BY_ROOT(empno) empno,
NTH_VALUE(ename, 2) OVER(PARTITION BY CONNECT_BY_ROOT(ename) ORDER BY LEVEL) n2,
NTH_VALUE(ename, 3) OVER(PARTITION BY CONNECT_BY_ROOT(ename) ORDER BY LEVEL) n3
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT n1 name1, MAX(n2) name2, MAX(n3) name3
FROM x
GROUP BY n1, empno
ORDER BY empno```

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.