## List all employees in 2 columns based on the salary ranking.

Problem: List all employee names and their respective salaries in 2 columns based in the salary ranking (from the highest to the lowest).

Expected Result:

``` ID LEFT_NAME      LEFT_SAL RIGHT_NAME    RIGHT_SAL
--- ------------ ---------- ------------ ----------
1 KING               5000 FORD               3000
2 SCOTT              3000 JONES              2975
3 BLAKE              2850 CLARK              2450
4 ALLEN              1600 TURNER             1500
5 MILLER             1300 WARD               1250
7 JAMES               950 SMITH               800
```

Solution:
I have picked 5 best performing methods to solve this problem. The idea behind each method can be found in my book: “Oracle SQL Tricks and Workarounds”

Method/Workaround #1: Using Hierarchical Query (Level: Advanced)

```WITH X AS (
SELECT ename, sal, ROW_NUMBER()OVER(ORDER BY sal DESC) RN
FROM EMP
)
SELECT  rn/2 AS id, PRIOR ename left_name, PRIOR sal left_sal, ename right_name, sal right_sal
FROM X
WHERE MOD(level,2)=0
CONNECT BY rn=1+PRIOR rn
```

Method/Workaround #2: Using Analytical Function (Level: Advanced)

```WITH X AS (
SELECT ename left_name, sal left_sal,
LEAD(ename, 1) OVER(ORDER BY sal DESC) AS right_name,
LEAD(sal, 1) OVER(ORDER BY sal DESC) as right_sal,
ROW_NUMBER() OVER(ORDER BY sal DESC) rn
from emp
)
SELECT (rn+1)/2 AS ID, left_name, left_sal,
right_name, right_sal
FROM X
WHERE MOD(rn,2)=1
ORDER BY rn
```

Method/Workaround #3: Using PIVOT Clause (Level: Advanced)

```SELECT *
FROM (SELECT CEIL(rn/2) AS ID, ename, sal, 2-MOD(rn,2) AS col_no
FROM (SELECT ename, sal, ROW_NUMBER() OVER(ORDER BY sal DESC) rn
FROM emp
)
)
PIVOT (MAX(ename) AS name,
MAX(sal)   AS sal
FOR (col_no) IN (1 AS left, 2 AS right)
)
ORDER BY 1
```

Method/Workaround #4: Using MAX function on concatenated column expression (Level: Advanced)

```WITH X AS (
SELECT LPAD(sal, 5, '0') || ename as sname, ROW_NUMBER()OVER(ORDER BY sal DESC) rn
FROM EMP
)
SELECT CEIL(rn/2) ID, SUBSTR(MAX(SNAME), 6) left_name,  TO_NUMBER(SUBSTR(MAX(SNAME), 1, 5)) left_sal,
SUBSTR(MIN(SNAME), 6) right_name, TO_NUMBER(SUBSTR(MIN(SNAME), 1, 5)) right_sal
FROM X
GROUP BY CEIL(rn/2)
ORDER BY 1
```

Method/Workaround #5: Using Self-Join (Level: Intermediate)

```WITH X AS (
SELECT ename, sal, ROW_NUMBER()OVER(ORDER BY sal DESC) rn
FROM EMP
)
SELECT B.rn/2 AS ID, a.ename AS left_name, a.sal AS left_sal,
b.ename AS right_name, b.sal AS right_sal
FROM x a LEFT JOIN x b ON a.rn+1=b.rn
WHERE mod(a.rn,2)=1
```

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

## SQL puzzle: Find unique specialists in every department

Puzzle of the day.
This is a fairly simple problem but from time to time I am being approached by developers who need help with very similar problems.

Find all employees who has a unique job title in their respective department.

Solution #1: Using NOT EXISTS

```SELECT ename, deptno, job, sal
FROM emp a
WHERE NOT EXISTS(SELECT 1
FROM emp b
WHERE a.deptno=b.deptno
AND a.job=b.job
AND a.empno!=b.empno)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850
```

Solution #1.1 – Generic substitution for NOT EXISTS

```SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT COUNT(b.empno)
FROM emp b
WHERE a.deptno=b.deptno
AND a.job=b.job
AND a.empno!=b.empno)
ORDER BY deptno, job
```

Solution #2: Using NOT IN

```SELECT ename, deptno, job, sal
FROM emp a
WHERE job NOT IN(SELECT job
FROM emp b
WHERE a.deptno=b.deptno
AND a.empno!=b.empno)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850
```

Solution #2.1: Using NOT IN with Multi-column subquery – it is pretty much the same as Solution #2.

```SELECT ename, deptno, job, sal
FROM emp a
WHERE (job, deptno) NOT IN(SELECT job, deptno
FROM emp b
WHERE a.empno!=b.empno)
ORDER BY deptno, job
```

Solution #3.1: Using COUNT in subquery (very similar to Solution #1.1 but has different execution plan)

```SELECT ename, deptno, job, sal
FROM emp a
WHERE 1=(SELECT COUNT(b.empno)
FROM emp b
WHERE a.deptno=b.deptno
AND a.job=b.job)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850
```

Solution #3.2: A mixed version of Solutions #1.1 and #3.1:

```SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT SUM(CASE WHEN a.empno=b.empno THEN 0 ELSE 1 END)
FROM emp b
WHERE a.deptno=b.deptno
AND a.job=b.job)
ORDER BY deptno, job
```

Solution #4: Using Analytical function COUNT

```WITH x AS (
SELECT ename, deptno, job, sal, COUNT(*) OVER(PARTITION BY deptno, job) cnt
FROM emp a
)
SELECT ename, deptno, job, sal
FROM x
WHERE cnt=1
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850
```

Solution #4.1 – using MIN/MAX analytical functions – essentially, it is the same as solution #4

```WITH x AS (
SELECT ename, deptno, job, sal,
MAX(empno) OVER(PARTITION BY deptno, job) max_no,
MIN(empno) OVER(PARTITION BY deptno, job) min_no
FROM emp a
)
SELECT ename, deptno, job, sal
FROM x
WHERE max_no=min_no
ORDER BY deptno, job
```

Solution #5: Using In-Line view (WITH)

```WITH x AS (
SELECT deptno, job
FROM emp
GROUP BY deptno, job
HAVING COUNT(*)=1
)
SELECT ename, deptno, job, sal
FROM emp JOIN x USING (deptno, job)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850
```

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

## Undocumented Oracle optimizer hint “MATERIALIZE”, by Zahar Hilkevich

Every time you use an in-line view or a WITH clause Oracle optimizer may (or may not) choose to use a temporary table based on the in-line view result set.

For example, for the following query Oracle optimizer does not use a temp table:

```WITH b AS
(SELECT deptno, MAX(sal) max_sal
FROM emp
GROUP BY deptno
)
SELECT a.ename, a.sal, a.deptno
FROM emp a JOIN b ON a.deptno=b.deptno AND a.sal=b.max_sal```

Execution Plan:

```Plan hash value: 269884559

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    39 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     1 |    39 |     8  (25)| 00:00:01 |
|   2 |   VIEW               |      |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP  |    14 |   182 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------```

To force Oracle optimizer using a temporary table instead of in-line view use materialize Oracle optimizer hint:

```WITH b AS
(SELECT /*+materialize */ deptno, MAX(sal) max_sal
FROM emp
GROUP BY deptno
)
SELECT a.ename, a.sal, a.deptno
FROM emp a JOIN b ON a.deptno=b.deptno AND a.sal=b.max_sal```

Execution Plan:

```Plan hash value: 3092985208

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     3 |   117 |    10  (20)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6A06_F425182A |       |       |            |          |
|   3 |    HASH GROUP BY           |                             |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL      | EMP                         |    14 |    98 |     3   (0)| 00:00:01 |
|*  5 |   HASH JOIN                |                             |     3 |   117 |     6  (17)| 00:00:01 |
|   6 |    VIEW                    |                             |     3 |    78 |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6A06_F425182A |     3 |    21 |     2   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL       | EMP                         |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------```

P.S. If you like this tip, you can find many more in my book “Oracle SQL Tricks and Workarounds”

## 15 Workarounds for Getting Top Records

To illustrate the concept we will be solving the following problem defined for scott schema:

Find all top paid employees in each department. Display employee names, salaries, jobs, and department.

To qualify for a workaround, a solution’s execution plan should have a distinct hash value (More on that can be found in my book “Oracle SQL Tricks and Workarounds”).

## Workaround #1: Correlated subquery

```SELECT ename, job, sal, deptno
FROM emp a
WHERE sal=(SELECT MAX(sal)
FROM emp b
WHERE b.deptno=a.deptno);

Result:

ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
BLAKE      MANAGER         2850         30
SCOTT      ANALYST         3000         20
KING       PRESIDENT       5000         10
FORD       ANALYST         3000         20

Execution Plan:

Plan hash value: 1245077725

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    47 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |         |     1 |    47 |     8  (25)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1 |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |         |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP     |    14 |    98 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP     |    14 |   294 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

```

## Workaround #2: Correlated subquery with arithmetic transformation

```SELECT ename, job, sal, deptno
FROM emp a
WHERE 0=(SELECT MAX(b.sal)-a.sal
FROM emp b
WHERE b.deptno=a.deptno)

Execution Plan:

Plan hash value: 2649664444

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    21 |    24   (0)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP  |    14 |   294 |     3   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  4 |    TABLE ACCESS FULL| EMP  |     5 |    35 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

```

## Workaround #3: Non-Correlated subquery

```SELECT ename, job, sal, deptno
FROM emp a
WHERE (deptno, sal) IN (SELECT deptno, MAX(sal)
FROM emp
GROUP BY deptno)

Execution Plan:

Plan hash value: 2491199121

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    47 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN SEMI      |          |     1 |    47 |     8  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | EMP      |    14 |   294 |     3   (0)| 00:00:01 |
|   3 |   VIEW               | VW_NSO_1 |     3 |    78 |     4  (25)| 00:00:01 |
|   4 |    HASH GROUP BY     |          |     3 |    21 |     4  (25)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| EMP      |    14 |    98 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

```

## Workaround #4: Aggregating over Cartesian Product

```SELECT a.ename, a.job, a.sal, a.deptno
FROM emp a, emp b
WHERE a.deptno=b.deptno
GROUP BY a.ename, a.job, a.sal, a.deptno
HAVING a.sal=MAX(b.sal)

Execution Plan:

Plan hash value: 2435006919

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     6 |   168 |     8  (25)| 00:00:01 |
|*  1 |  FILTER              |      |       |       |            |          |
|   2 |   HASH GROUP BY      |      |     6 |   168 |     8  (25)| 00:00:01 |
|*  3 |    HASH JOIN         |      |    65 |  1820 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |   294 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

```

## Workaround #5: Outer Join with IS NULL check

```SELECT a.ename, a.job, a.sal, a.deptno
FROM emp a LEFT JOIN emp b ON a.deptno=b.deptno
AND a.sal<b.sal
WHERE b.empno IS NULL

Execution Plan:

Plan hash value: 1201587841

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    32 |     7  (15)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|*  2 |   HASH JOIN OUTER   |      |     1 |    32 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   294 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |   154 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

```

## Workaround #6: Using NOT EXISTS

```SELECT ename, job, sal, deptno
FROM emp a
WHERE NOT EXISTS (SELECT 1
FROM emp b
WHERE b.deptno=a.deptno
AND b.sal>a.sal)

Execution Plan:

Plan hash value: 3353202012

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   140 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     5 |   140 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   294 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
```

## Synonymous Workaround for #6 (execution plan has the same hash value): Using COUNT(*)=0 Equivalent

```SELECT ename, job, sal, deptno
FROM emp a
WHERE 0=(SELECT COUNT(*)
FROM emp b
WHERE b.deptno=a.deptno
AND b.sal>a.sal)

Execution Plan:

Plan hash value: 3353202012```

## Workaround #7: Using ALL Predicate

```SELECT ename, job, sal, deptno
FROM emp a
WHERE a.sal>=ALL(SELECT b.sal
FROM emp b
WHERE b.deptno=a.deptno)

Execution Plan:

Plan hash value: 2561671593

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   294 |    24   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   294 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     2 |    14 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
```

## Workaround #8: Using In-Line View

```SELECT a.ename, a.sal, a.deptno
FROM emp a, (SELECT deptno, MAX(sal) max_sal
FROM emp
GROUP BY deptno) b
WHERE a.deptno=b.deptno
AND a.sal=b.max_sal

Execution Plan:

Plan hash value: 269884559

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    39 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     1 |    39 |     8  (25)| 00:00:01 |
|   2 |   VIEW               |      |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP  |    14 |   182 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
```

## Workaround #9: Using EXISTS Predicate

```SELECT ename, job, sal, deptno
FROM emp a
WHERE EXISTS (SELECT 1
FROM emp b
WHERE b.deptno=a.deptno
HAVING a.sal=MAX(b.sal))

Execution Plan:

Plan hash value: 3057787348

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    21 |    24   (0)| 00:00:01 |
|*  1 |  FILTER              |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | EMP  |    14 |   294 |     3   (0)| 00:00:01 |
|*  3 |   FILTER             |      |       |       |            |          |
|   4 |    SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  5 |     TABLE ACCESS FULL| EMP  |     5 |    35 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
```

## Synonymous Workaround for #9 (execution plan has the same hash value): Using COUNT(*)>0 Equivalent

```SELECT ename, job, sal, deptno
FROM emp a
WHERE 0< (SELECT COUNT(1)
FROM emp b
WHERE b.deptno=a.deptno
HAVING a.sal=MAX(b.sal))

Execution Plan:

Plan hash value: 3057787348
```

## Here is a practical example which happens to qualify as another Synonymous Workaround for #9:

```SELECT ename, job, sal, deptno
FROM emp a
WHERE NOT EXISTS (SELECT 1
FROM emp b
WHERE b.deptno=a.deptno
HAVING a.sal<MAX(b.sal))```

Execution Plan:

`Plan hash value: 3057787348`

## Workaround #10: Using Analytical Function RANK()

```WITH x AS (
SELECT ename, job, sal, deptno,
RANK()OVER(PARTITION BY deptno ORDER BY sal DESC) rk
FROM emp a
)
SELECT ename, job, sal, deptno
FROM x
WHERE rk=1

Execution Plan:

Plan hash value: 3291446077

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    14 |   728 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                    |      |    14 |   728 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      |    14 |   294 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMP  |    14 |   294 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

```

## Workaround #11: Using Analytical Function MAX

```WITH x AS (
SELECT ename, job, sal, deptno,
MAX(sal)OVER(PARTITION BY deptno) max_sal
FROM emp a
)
SELECT ename, job, sal, deptno
FROM x
WHERE sal=max_sal

Execution Plan:

Plan hash value: 4130734685

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   728 |     4  (25)| 00:00:01 |
|*  1 |  VIEW               |      |    14 |   728 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT       |      |    14 |   294 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   294 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
```

## Workaround #12: Using Analytical Function COUNT with CONNECT BY

```WITH x AS (
SELECT ename, job, sal, deptno, COUNT(*)OVER(PARTITION BY empno) cnt
FROM emp a
CONNECT BY deptno=PRIOR deptno
AND sal<PRIOR sal
)
SELECT ename, job, sal, deptno
FROM x
WHERE cnt=1```

Execution Plan:

```Plan hash value: 704858046

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |    14 |   728 |     2   (0)| 00:00:01 |
|*  1 |  VIEW                          |        |    14 |   728 |     2   (0)| 00:00:01 |
|   2 |   WINDOW SORT                  |        |    14 |   350 |     2   (0)| 00:00:01 |
|*  3 |    CONNECT BY WITHOUT FILTERING|        |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   350 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
```

## Workaround #13: Using Analytical Function COUNT with CONNECT BY filtered by LEVEL

```WITH x AS (
SELECT ename, job, sal, deptno, COUNT(*)OVER(PARTITION BY empno) cnt
FROM emp a
WHERE level<=2
CONNECT BY deptno=PRIOR deptno
AND sal<PRIOR sal
)
SELECT ename, job, sal, deptno
FROM x
WHERE cnt=1

Execution Plan:

Plan hash value: 2668428643

------------------------------------------------------------------------------------------
| Id  | Operation                       | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |        |    14 |   728 |     2   (0)| 00:00:01 |
|*  1 |  VIEW                           |        |    14 |   728 |     2   (0)| 00:00:01 |
|   2 |   WINDOW SORT                   |        |    14 |   350 |     2   (0)| 00:00:01 |
|*  3 |    FILTER                       |        |       |       |            |          |
|*  4 |     CONNECT BY WITHOUT FILTERING|        |       |       |            |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   350 |     2   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
```

## Workaround #14: CONNECT BY with GROUP BY and HAVING

```SELECT ename, job, sal, deptno
FROM emp a
CONNECT BY deptno=PRIOR deptno
AND sal<PRIOR sal
GROUP BY ename, job, sal, deptno
HAVING COUNT(*)=1

Execution Plan:

Plan hash value: 2144516570

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |     1 |    21 |     4  (25)| 00:00:01 |
|*  1 |  FILTER                        |      |       |       |            |          |
|   2 |   HASH GROUP BY                |      |     1 |    21 |     4  (25)| 00:00:01 |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     TABLE ACCESS FULL          | EMP  |    14 |   294 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
```

## Workaround #15: GROUP BY and HAVING over CONNECT BY filtered by LEVEL

```SELECT ename, job, sal, deptno
FROM emp a
WHERE level<=2
CONNECT BY deptno=PRIOR deptno
AND sal<PRIOR sal
GROUP BY ename, job, sal, deptno
HAVING COUNT(*)=1

Execution Plan:

Plan hash value: 1946770371

----------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |     1 |    21 |     4  (25)| 00:00:01 |
|*  1 |  FILTER                         |      |       |       |            |          |
|   2 |   HASH GROUP BY                 |      |     1 |    21 |     4  (25)| 00:00:01 |
|*  3 |    FILTER                       |      |       |       |            |          |
|*  4 |     CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   5 |      TABLE ACCESS FULL          | EMP  |    14 |   294 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
```

If you want to learn how to come up with numerous workarounds on your own, check my book “Oracle SQL Tricks and Workarounds” for instructions.

## Find a top record without using a subquery and in-line view/with clause, by Zahar Hilkevich

We will be finding a top paid employee from the scott’s emp table.

With a help of a subquery, the task becomes trivial:

```SELECT ename, job, sal
FROM emp
WHERE sal=(SELECT MAX(sal) FROM emp)```

Result:

```ENAME      JOB              SAL
---------- --------- ----------
KING       PRESIDENT       5000```

Alternatively, you can use an in-line view:

```SELECT ename, job, sal
FROM (SELECT ename, job, sal, RANK()OVER(ORDER BY sal DESC) rk
FROM emp)
WHERE rk=1```

Result:

```ENAME      JOB              SAL
---------- --------- ----------
KING       PRESIDENT       5000```

The exercise is to achieve the same without a help of “another” query which is usually provided by a subquery, in-line view, or WITH clause:

```SELECT a.ename, a.job, a.sal
FROM emp a, emp b
GROUP BY a.ename, a.job, a.sal
HAVING a.sal=MAX(b.sal)```

Result:

```ENAME      JOB              SAL
---------- --------- ----------
KING       PRESIDENT       5000```

We have just used a Cartesian Product of two instances of the emp table!

You can read about 15 Workarounds for Getting Top Records in my new post.

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