Sql Query Question

I Don't Have SQL to hand to test by think something like this would work;

Select Top 1 * From ( Select Top 2 * From Salaries Order By Salary Desc) Order By Salary Asc;

Which should in theory give you the 2nd highest salary, by first creating a set of highest 2 salaries and then flipping the order and returning the lowest.

Here is a code to get the N-th highest/lowest salary if you use MS Sql Server 2005/2008

-- N-th highest salary
SELECT  TOP 1
        SalaryAmount
FROM    (
        SELECT  SalaryRank  = ROW_NUMBER() OVER(ORDER BY SalaryAmount DESC),
                SalaryAmount
        FROM    Salary
        ) a
WHERE   SalaryRank = 2 --N

-- N-th lowest salary
SELECT  TOP 1
        SalaryAmount
FROM    (
        SELECT  SalaryRank  = ROW_NUMBER() OVER(ORDER BY SalaryAmount),
                SalaryAmount
        FROM    Salary
        ) a
WHERE   SalaryRank = 2 --N

Hope this help!

i got the answer

select max(amount) from salary where amount not in (select max(amount) from salary)