Archive for the tag 'self-join'

Self-Join sub query

Posted March 9, 2006 * Comments(0)

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.

Tags: , , ,