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

Advertisements

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.

    Keep your posts coming!

    • mccs2000 August 23, 2013 / 12:05 pm

      Analytical functions open a whole new dimension in what can be done in Oracle SQL, but this is a subject of a different post. Again, the trick was not to use subqueries and in-line views that are often necessary with the use of analytical functions.

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