Interview Question: Get top and bottom paid employees in each department

This is a typical interview problem: list all bottom and top paid employees in each department. A preferred solution should not be using UNION or UNION ALL operators.

Please watch this short video to learn a couple of non-obvious techniques and to impress your potential employers on your next job interview.

My Oracle Group on Facebook:

If you like this post, you may want to join my 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

One thought on “Interview Question: Get top and bottom paid employees in each department

  1. pradeep February 26, 2018 / 1:22 am

    WITH temp AS
    (SELECT * FROM emp_copy
    ),
    xx AS
    (SELECT MAX(sal) maxsal,MIN(sal) minsal,deptno FROM temp where sal is not null GROUP BY deptno
    )
    SELECT t.ename,t.sal,t.deptno
    FROM XX x,
    temp t
    WHERE t.deptno= x.deptno
    AND sal IN (x.maxsal,x.minsal)
    order by t.deptno;

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s