Self-Join sub query

I came across a unique requirement at work for which I struggled a lot to write the SQL query. Then after some googling and tips from my DBA friends I finally came up with the query. I never came across this query before, so I thought of posting about it here, since some of you might benefit from it.

Since I cannot tell you the exact requirement or the table structure I mapped it with the classic emp table to illustrate the query.

Ok so consider the following table structure

SEQ_ID - unique sequence number
EMP_ID - unique emp id
DEP_ID - department id (from a master table)
SAL    - salary

And now the requirement is to find the employee with the max salary in each department.

The query

SELECT dep_id, MAX(sal) FROM tbl_name GROUP BY dep_id

will give me the maximum salary in each department, but now the real problem is to find out the person with that maximum salary in each department.

So finally I came up with this query

SELECT * FROM tbl_name WHERE (dept_id, sal) IN (SELECT dep_id, MAX(sal) FROM tbl_name GROUP BY dept_id)

The reason why I found this syntax to be strange for me is that I have never used two parameters in the IN clause.

The above query works, (atleast for me in Oracle 9i) but I am not sure whether it is the most optimal way of doing it. So if any of you know a better solution then do let me know.

Update:Jeff in the comments has specified that the equivalent in SQL Server is

SELECT A,* FROM tbl_name A
INNER JOIN (SELECT dept_id, MAX(Sal) AS Salary FROM tbl_name GROUP BY dept_id) B
ON B.dept_id = A.dept_id AND B.Sal = A.Sal

Related posts

Tags: , , ,

2 Comments so far

Follow up comments through RSS Feed | Post a comment

  • Jeff says:

    Here is the code for SQL Server.

    SELECT A,* FROM tbl_name A
    INNER JOIN (SELECT dept_id, MAX(Sal) AS Salary FROM tbl_name GROUP BY dept_id) B
    ON B.dept_id = A.dept_id AND B.Sal = A.Sal

Leave a Reply to Sudar Cancel reply

Your email address will not be published. Required fields are marked *