## Interview Question

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.