Interview Question: How to Retrieve Unique Values without Using DISTINCT keyword

Question: List unique jobs (from emp table) without using DISTINCT keyword

Question Level: Beginner+

We picked 5 different methods (workarounds) to show in this post. It is possible to produce at least 15 if you read some other posts in this blog.

Method/Workaround #1: Use GROUP BY (Level: Beginner)

```SELECT job
FROM emp
GROUP BY job
```

Method/Workaround #2: Use Analytical functions with a subquery (Level: Intermediate)

```SELECT job
FROM(SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
FROM emp)
WHERE rn=1
```

Method/Workaround #3: Use correlated subquery (Level: Intermediate)

```SELECT job
FROM emp a
WHERE empno=(SELECT MAX(empno)
FROM emp
WHERE job=a.job)
```

Method/Workaround #4: Use left join with IS NULL filter (Level: Advanced)

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

Method/Workaround #5: Use NOT EXIST (Level: Intermediate)

```SELECT job
FROM emp a
WHERE NOT EXISTS(SELECT 1
FROM emp b
WHERE a.job=b.job
AND b.empno>a.empno)
```

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