Interview Question: Show Odd/Even rows without using any functions and pseudo-columns

Sushil Kumar, Database Developer at JP Morgan Chase & Co, has recently asked me this question on my Facebook group page. My first reaction was: “What a silly question! Of course it is impossible to identify odd and even rows without using functions”. But shortly after that, I realized that this is a great SQL puzzle. It took me about 30 minutes (which is a lot!) to solve it.

Interview Question: Show Odd/Even rows without using any functions and pseudo-columns

Level: Advanced

Sample Expected Result:

ENAME           EMPNO   ODD_EVEN
---------- ---------- ----------
MILLER           7934          1
FORD             7902          0
JAMES            7900          1
ADAMS            7876          0
TURNER           7844          1
KING             7839          0
SCOTT            7788          1
CLARK            7782          0
BLAKE            7698          1
MARTIN           7654          0
JONES            7566          1
WARD             7521          0
ALLEN            7499          1
SMITH            7369          0

--Note: Rows are sorted by empno

The idea behind the following solution is quite simple: substitute functions with operators and predicates. Several similar techniques were described in my book Oracle SQL Tricks and Workarounds.

Solution:

WITH x (ename, empno, odd_even) AS
(
SELECT ename, empno, 1 as odd_even
FROM emp
WHERE empno>=ALL(SELECT empno FROM emp)
UNION ALL
SELECT e.ename, e.empno, 1-odd_even
FROM emp e, x
WHERE e.empno>=ALL(SELECT empno FROM emp WHERE empno<x.empno)
)
SELECT *
FROM x
/

ENAME           EMPNO   ODD_EVEN
---------- ---------- ----------
MILLER           7934          1
FORD             7902          0
JAMES            7900          1
ADAMS            7876          0
TURNER           7844          1
KING             7839          0
SCOTT            7788          1
CLARK            7782          0
BLAKE            7698          1
MARTIN           7654          0
JONES            7566          1
WARD             7521          0
ALLEN            7499          1
SMITH            7369          0


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.

Advertisements

Puzzle of the Week Challenge

Today we are starting a new challenge – every week we will publish a puzzle (various levels).

There will be some awards and signs of recognition to the winners.

Puzzle of the week:
Level: Advanced

jigsaw-puzzle-piece Write a single SELECT statement that would output a calendar for the current month in a traditional tabular format (7 columns: Sun-Sat).

Expected result may look like this (in SQL*Plus):

p01-2

or this:

p01-2

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

 

Interview Question: Can a Select statement have HAVING clause and no GROUP BY?

Question: Can a Select statement have HAVING clause and no GROUP BY?
Level: Intermediate/Advanced.

I was first asked this question about 20 years ago and I have to admit I did not provide the right answer which seemed to be very counter intuitive.

The correct answer: Yes, it can.

Example:

SELECT SUM(sal)
FROM emp
HAVING COUNT(*)>10

Essentially, when we don’t use GROUP BY clause we treat the entire table as a single group. As with any group, we can reference various aggregate functions in SELECT, HAVING, and ORDER BY clauses.

We can even use some analytical functions as follows:

SELECT COUNT(*), SUM(COUNT(*)) OVER() sm
FROM emp
HAVING COUNT(*)<100

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.

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
  6 MARTIN             1250 ADAMS              1100
  7 JAMES               950 SMITH               800

Problem Level: Intermediate/Advanced

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
START WITH rn=1
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.

Interview Question: Get the 2nd highest salary in the company

Question: Get the 2nd highest salary in the company.

Question level: Intermediate

We picked just 6 workarounds for this fairly simple problem. Overall, there are at least 15 different approaches available.

Method/Workaround #1 (Level: Beginner)

SELECT MAX(SAL)
FROM (SELECT SAL
      FROM EMP
      WHERE SAL<(SELECT MAX(SAL) 
                 FROM EMP)
     )

Method/Workaround #2 (Level: Intermediate)

SELECT sal
FROM (SELECT sal, ROWNUM rn
      FROM (SELECT SAL
            FROM EMP
            GROUP BY SAL
            ORDER BY 1 DESC)
      WHERE ROWNUM<=2
      )
WHERE RN=2 

Method/Workaround #3 (Level: Intermediate)

SELECT DISTINCT sal
FROM (SELECT SAL, DENSE_RANK()OVER(ORDER BY SAL DESC) RK
      FROM EMP)
WHERE RK=2      

Method/Workaround #4 (Level: Intermediate)

SELECT SAL
FROM (SELECT DISTINCT SAL, DENSE_RANK()OVER(ORDER BY SAL DESC) RK
      FROM EMP)
WHERE RK=2     

Method/Workaround #5 (Level: Advanced)

SELECT A.SAL
FROM EMP A JOIN EMP B ON A.SAL<=b.SAL
GROUP BY a.sal
HAVING COUNT(DISTINCT b.sal)=2

Method/Workaround #6 (Level: Intermediate)

SELECT DISTINCT SAL
FROM emp a
WHERE 2=(SELECT COUNT(DISTINCT sal)
         FROM emp b
         WHERE b.sal>=a.sal) 

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