Interview Question: get 2 random employees for each salary range?

Interview Question

Level: Intermediate/Advanced

For each of the following salary ranges select two randomly chosen employees:

0-999
1000-1999
2000-2999
3000+

Expected Result:

ENAME             SAL RANGE
---------- ---------- ---------
SCOTT            3000 3000+
FORD             3000 3000+
BLAKE            2850 2000-2999
CLARK            2450 2000-2999
TURNER           1500 1000-1999
MILLER           1300 1000-1999
JAMES             950 0-999
SMITH             800 0-999

Solution:

WITH x AS (
SELECT ename, sal,
       CASE WHEN sal>=3000 THEN '3000+'
            WHEN sal>=2000 THEN '2000-2999'
            WHEN sal>=1000 THEN '1000-1999'
            ELSE                '0-999'
       END as range,
       ROW_NUMBER() OVER(PARTITION BY DECODE(GREATEST(sal, 3000), sal, 0, 1) +
                                      DECODE(GREATEST(sal, 2000), sal, 0, 1) +
                                      DECODE(GREATEST(sal, 1000), sal, 0, 1)
                         ORDER BY DBMS_RANDOM.VALUE) rn
FROM emp
)
SELECT ename, sal, range
FROM x
WHERE rn<=2
ORDER BY sal DESC

 

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” for instructions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s