## Find a top record without using a subquery and in-line view/with clause, by Zahar Hilkevich

We will be finding a top paid employee from the scott’s emp table.

With a help of a subquery, the task becomes trivial:

```SELECT ename, job, sal
FROM emp
WHERE sal=(SELECT MAX(sal) FROM emp)```

Result:

```ENAME      JOB              SAL
---------- --------- ----------
KING       PRESIDENT       5000```

Alternatively, you can use an in-line view:

```SELECT ename, job, sal
FROM (SELECT ename, job, sal, RANK()OVER(ORDER BY sal DESC) rk
FROM emp)
WHERE rk=1```

Result:

```ENAME      JOB              SAL
---------- --------- ----------
KING       PRESIDENT       5000```

The exercise is to achieve the same without a help of “another” query which is usually provided by a subquery, in-line view, or WITH clause:

```SELECT a.ename, a.job, a.sal
FROM emp a, emp b
GROUP BY a.ename, a.job, a.sal
HAVING a.sal=MAX(b.sal)```

Result:

```ENAME      JOB              SAL
---------- --------- ----------
KING       PRESIDENT       5000```

We have just used a Cartesian Product of two instances of the emp table!

You can read about 15 Workarounds for Getting Top Records in my new post.

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

### 3 thoughts on “Find a top record without using a subquery and in-line view/with clause, by Zahar Hilkevich”

1. victoria August 19, 2013 / 8:40 am

great blog which i will be now following!

2. danstober August 23, 2013 / 12:54 am

Nice blog!

I like this solution with an analytic function even better because it avoids a self-join altogether:
``` SQL> SELECT ename, job, sal 2 FROM ( 3 SELECT ename, job, sal 4 , MAX ( sal ) OVER () maxsal 5 FROM emp 6* ) WHERE sal= maxsal SQL> /```

``` ENAME JOB SAL ---------- --------- ---------- KING PRESIDENT 5000 ```

```1 row selected. ```