How to find the second maximum salary of an employee using query?

[Padma priya Gururajan]

Hi,

How can I find the second maximum salary of an employee using joins or query?

Thanks,
Padma priya N.G.

[Jan Cumps]

First find a query to find the maximum salary. Then create a query that finds the biggest salary that is smaller than the first one.

Or

Create a query that returns the salaries ordered by amount descending (highest amount comes first in the result). Then loop until you find the first salary that is smaller than the one in the first record.

[Paul Clapham]

Don't forget to account for situations where two (or more) employees have the same amount and it's the maximum, and where two (or more) employees have the same amount and it's the second-largest.

[Martin Vajsar]

Analytic functions can be used here, namely RANK or DENSE_RANK. It doesn't spare you of thinking about Paul's remarks, though.

See also the explanation of RANK and DENSE_RANK on AskTom.