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