# 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.

# 2018 Puzzle of the Week #2:

For each of the following salary ranges select two randomly chosen employees:
0-999
1000-1999
2000-2999
3000+

Expected Result (in SQL*Plus):

```ENAME      SAL        RANGE
---------- ---------- ---------
SCOTT            3000 3000+
FORD             3000 3000+
BLAKE            2850 2000-2999
CLARK            2450 2000-2999
TURNER           1500 1000-1999
MILLER           1300 1000-1999
JAMES             950 0-999
SMITH             800 0-999```
• Remember to use only a single SELECT statement.
• Use table emp (from Oracle scott schema)

# Solutions:

## Solution #1: Using ROW_NUMBER with random.value functions:

We are applying a random sorting order to each of the salary ranges and take 2 top records from each range:

```WITH x AS (
SELECT CASE WHEN sal<=999  THEN '0-999'
WHEN sal<=1999 THEN '1000-1999'
WHEN sal<=2999 THEN '2000-2999'
ELSE                '3000+'
END range,
ename, sal
FROM emp
), y AS (
SELECT ename, sal, range,
ROW_NUMBER()OVER(PARTITION BY range
ORDER BY dbms_random.value) rn
FROM x
)
SELECT range, ename, sal
FROM y
WHERE rn<=2
ORDER BY range```

Result:

```RANGE     ENAME      SAL
--------- ---------- ----------
0-999     JAMES      950
0-999     SMITH      800
1000-1999 WARD 1250
1000-1999 TURNER 1500
2000-2999 JONES 2975
2000-2999 CLARK 2450
3000+     FORD 3000
3000+     KING 5000```

Result (of subsequent execution):

```RANGE     ENAME             SAL
--------- ---------- ----------
0-999     SMITH             800
0-999     JAMES             950
1000-1999 WARD             1250
1000-1999 MARTIN           1250
2000-2999 BLAKE            2850
2000-2999 JONES            2975
3000+     SCOTT            3000
3000+     KING             5000```

## Solution #2: Using DECODE, MAX() KEEP and UNION ALL:

Instead of taking top 2 records (randomly sorted), we are taking top 1 and bottom 1 and combine them together. DECODE function mimics the CASE from the previous solution.

```WITH x AS (
SELECT DECODE(1, SIGN(999-sal), '0-999', SIGN(1999-sal), '1000-1999',
SIGN(2999-sal), '2000-2999', '3000+') range,
ename, sal, ROWNUM || dbms_random.value rnd
FROM scott.emp
)
SELECT range, MAX(ename)KEEP(DENSE_RANK FIRST ORDER BY rnd) ename,
MAX(sal)  KEEP(DENSE_RANK FIRST ORDER BY rnd) sal
FROM x
GROUP BY range
UNION ALL
SELECT range, MAX(ename)KEEP(DENSE_RANK LAST ORDER BY rnd) ename,
MAX(sal)  KEEP(DENSE_RANK LAST ORDER BY rnd) sal
FROM x
GROUP BY range
ORDER BY range
```

Result:

```RANGE     ENAME             SAL
--------- ---------- ----------
0-999     JAMES             950
0-999     SMITH             800
1000-1999 MARTIN           1250
1000-1999 WARD             1250
2000-2999 JONES            2975
2000-2999 BLAKE            2850
3000+     FORD             3000
3000+     KING             5000
```

Note, that we concatenated ROWNUM with dbms_random.value to produce UNIQUE random value. Without ROWNUM (or any other KEY) there is always a chance that dbms_random.value will repeat on different rows and hence top and bottom values could be mixed and the same employee will be repeated twice.

## Solution #3: Using SIN for random value simulation and multi-column UNPIVOT with MAX() KEEP function:

Instead of combining top and bottom records from two statements using UNION ALL, here were calculating top and bottom values as 1 record and UNPIVOT them to produce two rows per salary range:

```WITH x AS (
SELECT DECODE(1, SIGN(999-sal), '0-999', SIGN(1999-sal), '1000-1999',
SIGN(2999-sal), '2000-2999', '3000+') range,
ename, sal,
SIN(ROWNUM*TO_NUMBER(SUBSTR(
extract(second
from current_timestamp),-3))
) rnd
FROM scott.emp
), y AS (
SELECT range, MAX(ename)KEEP(DENSE_RANK FIRST ORDER BY rnd) ename1,
MAX(sal)  KEEP(DENSE_RANK FIRST ORDER BY rnd) sal1,
MAX(ename)KEEP(DENSE_RANK LAST ORDER BY rnd) ename2,
MAX(sal)  KEEP(DENSE_RANK LAST ORDER BY rnd) sal2
FROM x
GROUP BY range
)
SELECT range, ename, sal
FROM y
UNPIVOT (
(ename, sal) for (t1, t2) in ((ename1,sal1), (ename2,sal2))
)
ORDER BY range
```

Result:

```RANGE     ENAME             SAL
--------- ---------- ----------
0-999     SMITH             800
0-999     JAMES             950
1000-1999 MILLER           1300
1000-1999 MARTIN           1250
2000-2999 CLARK            2450
2000-2999 BLAKE            2850
3000+     FORD             3000
3000+     SCOTT            3000
```

Note the use of multi-column UNPIVOT. Randomization simulation is based on a fairly random selection of the last 3 digits in the current timestamp’s second value. This number is used as a “seed”. When this seed is multiplied by the rownum, the result is used as a SIN function argument which makes the outcome pseudo-random.

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

### 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.