# Puzzle of the Week #5:

### Find the shortest and longest last names of the employees in each department.

• If two or more employees tie for the shortest or longest name, pick the name that comes first in alphabetical order
• Use hr.employees or scott.emp tables
• Use a single SELECT statement only
• Ideally, the solution should NOT rely on any sub-queries, CTEs  (WITH clause), or inline views
• Exclude unknown (NULL) departments

## Solutions:

#1. Using MIN() KEEP Group Function

```SELECT department_id,
MIN(last_name) KEEP(DENSE_RANK FIRST
ORDER BY LENGTH(last_name)) shortest,
MIN(last_name) KEEP(DENSE_RANK FIRST
ORDER BY LENGTH(last_name) DESC) longest
FROM hr.employees
WHERE department_id IS NOT NULL
GROUP BY department_id```

#2. Using FIRST_VALUE Analytic Function and DISTINCT option

(Credit to Igor Shpungin)

```SELECT DISTINCT department_id,
FIRST_VALUE(last_name) OVER(PARTITION BY department_id
ORDER BY LENGTH(last_name))      shortest,
FIRST_VALUE(last_name) OVER(PARTITION BY department_id
ORDER BY LENGTH(last_name) DESC) longest
FROM hr.employees
WHERE department_id IS NOT NULL
ORDER BY 1```

#3. Using MODEL clause

(Credit to Naoto Katayama)

```SELECT department_id, shortest, longest
FROM hr.employees
WHERE department_id IS NOT NULL
MODEL
RETURN UPDATED ROWS
PARTITION BY (department_id)
DIMENSION BY (
ROW_NUMBER()OVER(PARTITION BY department_id
ORDER BY LENGTH(last_name), last_name) rn1,
ROW_NUMBER()OVER(PARTITION BY department_id
ORDER BY LENGTH(last_name) DESC, last_name) rn2)
MEASURES(last_name,
CAST(NULL AS VARCHAR2(25)) AS shortest,
CAST(NULL AS VARCHAR2(25)) AS longest)
RULES(
shortest[0,0]=MAX(last_name)[1,ANY],
longest [0,0]=MAX(last_name)[ANY,1]
)
ORDER BY department_id```

### My Oracle Group on Facebook:

If you like this post, you may want to join my 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.

Advertisements