How to find the second highest value from a table.?

Question

One table with EmpSalary table in employee table. I need to find the second largest salary that the company paid.

How to find the second highest value (Salary) from the table.?

Answer 1

Try the following: this should give the second highest salary:

SELECT MAX (EmpSalary) FROM employee WHERE EmpSalary <(SELECT MAX (EmpSalary) FROM employee);

Answer 2

;WITH CTE AS ( SELECT ROW_NUMBER() OVER (ORDER BY SortColumn DESC) AS RowNumber, * 
               FROM YourTable)
SELECT * FROM CTE WHERE RowNumber = 2

Answer 3

top (1) prodMrp Product, prodMrp = ( top (1) prodMrp prodMrp DESC) order by prodMrp DESC