Second highest value when using group by

From: Ajit <ajitsd_at_gmail.com>
Date: 6 Dec 2004 16:50:56 -0800
Message-ID: <1102380656.451212.8770@z14g2000cwz.googlegroups.com>

I want to find the second highest salary earner in each department in the example below:

ID Department Salary

1    101          400
2    101          550
3    101          500
4    201          400
5    201          500

The query I am expecting should return this result set:

ID Department Salary

3    101          500
4    201          400

Whats the most efficient way of writing SQL for such a problem? Received on Mon Dec 06 2004 - 18:50:56 CST