# Second Top Employee as of the Start of Employment

List all employees who were 2nd top paid in the entire company as of the time their employment started

• Use a single SELECT statement only.
• At the time of employment start the rank of the employee by salary should be 2.
• Show the top salary at the time when the employee started with the company.
• We assume that no employees have ever been terminated since day 1.

Expected Result:

ENAME JOB SAL HIREDATE MAX_SAL
WARD SALESMAN 1250 22-FEB-81 1600
BLAKE MANAGER 2850 01-MAY-81 2975
FORD ANALYST 3000 03-DEC-81 5000
SCOTT ANALYST 3000 19-APR-87 5000

# Solutions:

### Solution #1. Using LATERAL view, RANK and cumulative MAX analytic functions (Oracle 12g+):

``````SELECT e.ename, e.job, e.hiredate, e.sal, r.max_sal
FROM scott.emp e, LATERAL(SELECT a.empno,
RANK() OVER(ORDER BY a.sal DESC) rk,
MAX(a.sal) OVER() max_sal
FROM scott.emp a
WHERE a.hiredate<=e.hiredate) r
WHERE e.empno=r.empno
AND rk=2
ORDER BY e.hiredate``````

### Solution #2. Using CTE, cumulative MAX analytic function and a correlated subquery with COUNT to mimic the filter by RANK:

``````WITH x AS (
SELECT ename, job, hiredate, sal, MAX(sal)OVER(ORDER BY hiredate) max_sal
FROM scott.emp a
)
SELECT *
FROM x
WHERE 1=(SELECT COUNT(*)
FROM scott.emp
WHERE hiredate<=x.hiredate
AND sal>x.sal)
ORDER BY hiredate``````

### Solution #3. Using CTE, cumulative MAX analytic function and an in-line scalar subquery in SELECT to mimic the RANK:

``````WITH x AS (
SELECT ename, job, hiredate, sal, MAX(sal)OVER(ORDER BY hiredate) max_sal,
(SELECT COUNT(*)+1
FROM scott.emp
WHERE sal>e.sal
AND hiredate<=e.hiredate) rk
FROM scott.emp e
)
SELECT ename, job, hiredate, sal, max_sal
FROM x
WHERE rk=2
ORDER BY hiredate``````

### Solution #4. Using self-join and Cartesian Product with aggregation:

``````SELECT a.ename, a.job, a.hiredate, a.sal, MAX(b.sal) max_sal
FROM scott.emp a JOIN scott.emp b ON b.hiredate<=a.hiredate
AND b.sal>a.sal
GROUP BY a.ename, a.job, a.hiredate, a.sal
HAVING COUNT(DISTINCT b.empno)=1
ORDER BY a.hiredate``````

### Solution #5. Using CTE and cumulative MAX analytic function (twice):

``````WITH x AS (
SELECT ename, job, hiredate, sal,
MAX(sal) OVER(ORDER BY hiredate) max_sal
FROM scott.emp
), y  AS (
SELECT ename, job, hiredate, sal, max_sal, MAX(sal) OVER(ORDER BY hiredate) max_sal2
FROM x
WHERE sal<max_sal
)
SELECT ename, job, hiredate, sal, max_sal
FROM y
WHERE sal=max_sal2
ORDER BY hiredate``````

### Solution #6. Using regular and recursive CTEs, ROWNUM, GREATEST, and CASE functions (no Analytic functions!):

``````WITH e AS (
SELECT ename, job, sal, hiredate
FROM scott.emp
ORDER BY hiredate
), x AS (
SELECT ename, job, sal, hiredate, ROWNUM rn
FROM e
), y(max_sal, sal2, rn) AS (
SELECT sal, 0, 1
FROM x
WHERE rn=1
UNION ALL
SELECT GREATEST(x.sal, y.max_sal) AS max_sal,
CASE WHEN x.sal>y.max_sal THEN y.max_sal
WHEN x.sal>y.sal2 AND x.sal<=y.max_sal THEN x.sal
ELSE y.sal2
END AS sal2,
x.rn
FROM x JOIN y ON x.rn=y.rn+1
)
SELECT x.ename, x.job, x.sal, x.hiredate, y.max_sal
FROM y JOIN x ON y.rn=x.rn AND y.sal2=x.sal``````

### Solution #7. Using CTE and MODEL clause to mimic Solution #6:

``````WITH x AS (
SELECT *
FROM scott.emp
MODEL
DIMENSION BY (ROW_NUMBER() OVER(ORDER BY hiredate) rn)
MEASURES(ename, job, sal, hiredate, sal max_sal, 0 sal2)
RULES(
max_sal[rn>1] = GREATEST(max_sal[CV()-1], sal[CV()]),
sal2[rn>1] = CASE WHEN sal[CV()]> max_sal[CV()-1] THEN max_sal[CV()-1]
WHEN sal[CV()]> sal2[CV()-1]
AND sal[CV()]<=max_sal[CV()-1] THEN sal[CV()]
ELSE sal2[CV()-1]
END
)
)
SELECT ename, job, sal, hiredate, max_sal
FROM x
WHERE sal=sal2``````

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.

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

## How to delete duplicate records without using Joins and aggregate functions

Interview Question: How to delete duplicate records without using Joins and aggregate functions

```DELETE
FROM emp_dups a
WHERE ROWID<ANY(SELECT ROWID
FROM emp_dups b
WHERE b.empno=a.empno)
```

Explanation:

If we were allowed to use group functions, we could have used a very well known strategy:

```DELETE
FROM emp_dups a
WHERE ROWID<(SELECT MAX(ROWID)
FROM emp b
WHERE b.empno=a.empno)
```

Since group functions are not allowed, we need to find a substitution. In our case, the solution is based on the equivalence of the following 2 conditions:

```a<(SELECT MAX(a) ...)
a<ANY(SELECT a ...)
```

Another example of such equivalent conditions:

```a=(SELECT MAX(a) ...)
a>=ALL(SELECT a ...)
```

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

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

## Simulate LEAD and LAG functions using other analytic functions

Puzzle: Simulate LEAD and LAG functions using other analytic functions

Level: Intermediate

Solution:

Let’s say that we need to see every employee’s name and 2 more employees who were hired right before. The expected result may look like this:

```ENAME      HIREDATE  EMPL1      EMPL2
---------- --------- ---------- ---------
SMITH      17-DEC-80
ALLEN      20-FEB-81 SMITH
WARD       22-FEB-81 ALLEN      SMITH
JONES      02-APR-81 WARD       ALLEN
BLAKE      01-MAY-81 JONES      WARD
CLARK      09-JUN-81 BLAKE      JONES
TURNER     08-SEP-81 CLARK      BLAKE
MARTIN     28-SEP-81 TURNER     CLARK
KING       17-NOV-81 MARTIN     TURNER
JAMES      03-DEC-81 KING       MARTIN
FORD       03-DEC-81 JAMES      KING
MILLER     23-JAN-82 FORD       JAMES
SCOTT      19-APR-87 MILLER     FORD
```

It is a no-brainer task if we employ LAG function:

```SELECT ename, hiredate,
LAG(ename,1) OVER(ORDER BY hiredate) empl1,
LAG(ename,2) OVER(ORDER BY hiredate) empl2
FROM emp;
```

One of the possible approaches is to use ROWS window attribute with MIN/MAX analytic functions:

```SELECT ename, hiredate,
MAX(ename) OVER(ORDER BY hiredate ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) empl1,
MAX(ename) OVER(ORDER BY hiredate ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) empl2
FROM emp;
```

Common rule is:

```LAG(value_expr,offset,default) OVER ([partition_clause] order_by_clause )
```

is the same as

```NVL(MIN(value_expr)OVER ([partition_clause] order_by_clause
ROWS BETWEEN offset PRECEDING AND offset PRECEDING), default)
```

and

```LEAD(value_expr,offset,default) OVER ([partition_clause] order_by_clause )
```

is the same as

```NVL(MIN(value_expr)OVER ([partition_clause] order_by_clause
ROWS BETWEEN offset FOLLOWING AND offset FOLLOWING), default)
```

This substitution becomes essential in other RDBMS where MIN/MAX analytic functions are supported while LEAD/LAG are not. Teradata is one of the examples.

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.