how to get second most highest salary in emp table (sql query)?

Answer 1

You can do a Select query on a Select Query.

The first SELECT query returns a decending sort on salary and will return only X (2 in this case) records by using the TOP modifier (SELECT TOP 2)

You can run a second query on the first. If you use Access summation options in the query designer then you can specify FIRST or LAST record. LAST inthis case will be the second highest.

You can also reverse the sort direction to ACENDING and return the TOP 1 which will also be the second highest salary

Answer 2

The answer really depends on what database you are using, they don't all provide the same features.

You could try something like:

SELECT MIN salary FROM (SELECT TOP 2 salary FROM emp ORDER BY salary DESC)

This isn't particularly elegant, but if your database provides a 'TOP n' or 'FIRST n' it might do the job.

Answer 3

select max(salary) from emp
where salary < (select max(salary) from emp)