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```

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

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

For a given text string, find the first (from the beginning) longest sub-string that does not have repeating characters.

Solutions:

Solution #1: Using CONNECT BY clause (for range generation), REGEXP_COUNT, and RANK() functions:

```WITH w AS (
SELECT 'arkansas' AS word
FROM dual
), r AS (
SELECT ROWNUM rn
FROM w
CONNECT BY LEVEL<=LENGTH(word)
), x AS (
SELECT SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1) substr,
RANK() OVER(ORDER BY r2.rn - r1.rn DESC, r1.rn) rk
FROM r r1, r r2, w
WHERE r1.rn<=r2.rn
AND REGEXP_COUNT(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1), '(.).*\1') = 0
)
SELECT substr
FROM x
WHERE rk=1```

Result of execution in Oracle Live SQL client:

SUBSTR
rkans

Solution #2: Using CONNECT BY clause (for range generation), REGEXP_LIKE, and MAX() KEEP functions:

```WITH w AS (
SELECT 'arkansas' AS word
FROM dual
), r AS (
SELECT ROWNUM rn
FROM w
CONNECT BY LEVEL<=LENGTH(word)
)
SELECT MAX(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1))
KEEP(DENSE_RANK FIRST ORDER BY r2.rn - r1.rn DESC, r1.rn) substr
FROM r r1, r r2, w
WHERE r1.rn<=r2.rn
AND NOT REGEXP_LIKE(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1), '(.).*\1')```

Solution #3: Using CONNECT BY clause (twice), LATERAL view, REGEXP_COUNT, and RANK() functions:

```WITH w AS (
SELECT 'arkansas' AS word
FROM dual
), s AS (
SELECT SUBSTR(word, LEVEL) word, LEVEL rn
FROM w
CONNECT BY LEVEL<=LENGTH(word)
)
SELECT MAX(x.substr)
KEEP(DENSE_RANK FIRST ORDER BY LENGTH(x.substr) DESC, s.rn) substr
FROM s, LATERAL(SELECT SUBSTR(s.word, 1, LEVEL) substr
FROM dual
CONNECT BY LEVEL<=LENGTH(s.word)) x
WHERE REGEXP_COUNT(x.substr, '(.).*\1') = 0```

Solution #4: Using XMLTable function (for range generation), Correlated subquery with COUNT(DISTINCT), and MAX() KEEP function:

```WITH w AS (
SELECT 'arkansas' AS word
FROM dual
), r AS (
SELECT ROWNUM rn, word
FROM w, XMLTABLE('for \$i in 1 to \$N cast as xs:integer return \$i'
PASSING LENGTH(w.word) AS N) x
)
SELECT MAX(SUBSTR(r1.word, r1.rn, r2.rn - r1.rn + 1))
KEEP(DENSE_RANK FIRST ORDER BY r2.rn - r1.rn DESC, r1.rn) substr
FROM r r1, r r2
WHERE r1.rn<=r2.rn
AND r2.rn - r1.rn + 1 =
(SELECT COUNT(DISTINCT SUBSTR(SUBSTR(r1.word, r1.rn, r2.rn - r1.rn + 1),
LEVEL, 1))
FROM dual
CONNECT BY LEVEL<=r2.rn - r1.rn + 1
)```

Solution #5: Using CONNECT BY, Recursive CTE, INSTR, SUBSTR, and MAX() KEEP functions:

```WITH w AS (
SELECT 'arkansas' word
FROM dual
), s(sub, word, lvl, rn) AS (
SELECT SUBSTR(word, LEVEL, 1), SUBSTR(word, LEVEL) word, 1, ROWNUM
FROM w
CONNECT BY SUBSTR(word, LEVEL) IS NOT NULL
UNION ALL
SELECT SUBSTR(word, 1, lvl+1), word, lvl+1, ROWNUM
FROM s
WHERE LENGTH(SUBSTR(word, 1, lvl+1))=lvl+1
AND INSTR(sub, SUBSTR(word, lvl+1, 1))=0
)
SELECT MAX(sub) KEEP (DENSE_RANK FIRST ORDER BY lvl DESC, rn) substr
FROM s```

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.