Find second highest salary in sql server
Think about that carefully and see if you can yahoo messenger setup latest version come up with the actual SQL yourself before you read the answer that we provide below.
Consider below simple table, name Salary abc 1000000, how to find the employee whose salary is second highest.
If we remove the highest salary from a group of salary values, then we will have a new group of values whose highest salary is actually the 2nd highest in the original, employee table.
We have seen examples to get second highest salary in MySQL by using limit and without using limit.Select top 1 lary, fROM myTable t1, myTable t2, where lary lary.Employee table may contain duplicate salary, In this example there is no such record, so I have not used distinct.Weve now gone through many different solutions in different database vendors like Oracle, MySQL, and SQL Server.It will allow you to see first few rows, last few rows or range of rows.The 4th highest salary would have 3 salaries that are greater than.Then, adding the where Salary NOT IN in front basically creates a new set of Salary values that does not include the highest Salary value.Find the nth highest salary in Oracle using rank Oracle also provides a rank function that just assigns a ranking numeric value (with 1 being the highest) for some sorted values.Select MAX(salary) from Employee where Salary NOT IN ( select Max(Salary) from Employee This will return 200 in our case.More precisely, the entire row with the desired salary would be returned, and this is what it would look like: EmployeeID Salary 7 450 Its also worth pointing out that the reason distinct is used in the query above is because there may be duplicate.In MySQL without using limit keyword, in SQL Server without using TOP and in Oracle without using rank and rownum.Just like MySQL has limit keyword, which is immensely helpful in sorting and paging, Microsoft SQL Server also has a special keyword called TOP, which as name suggest prints top records from result set.
Select name, MAX(salary) AS salary from employee where salary (select MAX(salary) from employee where salary (select MAX(salary) from employee) This article is contributed by Kartik.Second Highest Salary in MySQL without limit.row_number over (order by Salary desc) rownumb from Employee Emp ) where rownumb n; n is nth highest salary The first thing you should notice in the query above is that inside the subquery the salaries are arranged in descending order.This query is actually quite similar to the one where we used the row_number analytic function, and works in the same way as well.The most important thing to understand in the query above is that the subquery is evaluated each and every time a row is processed by the outer query.You can retrieve the second highest salary using following two queries.And since the rows are arranged in descending order the row with the highest salary will have a 1 for the row number.Once try it Above Query, by, srinu.Treat my content as plain text, not as html.What if we try to exclude the highest salary value from the result set returned by the SQL that we run?Top 20 SQL queries from Interviews for some more fun.
The salary in the first row of the Employee table is 200.
Second Highest Salary using SQL Server Top Keyword.