N'th Highest Value from column in sql server using subquery;
Suppose we have one table name Employee and its two column :Id, Salary
Now if we want to find 5'th highest salary of emploee then query is:
SELECT TOP (1) Id
FROM (SELECT TOP (5) Salary
FROM Employee
ORDER BY Salary DESC) AS Salary
ORDER BY Salary
SELECT MAX(Salary) as 'Salary' from EmployeeDetails
ReplyDeletewhere Salary NOT IN
(
SELECT TOP 2 (SALARY) from EmployeeDetails ORDER BY Salary Desc
)
SELECT Name, Salary from ( SELECT Name, Salary, DENSE_RANK() over (order by Salary desc) AS srno
ReplyDeleteFROM tbl_ApplicationDetails) as Tbl
where srno =2
With This Query You Can Select Multiple Employee Whose Salary Is same .
For Example Is There Are 4 Employee Whose Salary Is 35000 Then All three User Will
Came out In OutPut
Ok Bye
In Above Example function Dense_rank() will create a srno order by salary. and if there is some one whose salary is same to another then it will give the same Srno to them. that's why we can see all that employee whose salary is same.
ReplyDeleteSELECT Name, Salary from (SELECT NAme, salary, row_number() over (order by Salary desc) AS srno
ReplyDeleteFROM tbl_ApplicationDetails) as tbl
where srno =3
in this query function row_number() will create a srno order by salary. difference with above query is that it will not return multiple user whose salary is same.
but this function help Us if we want get some record with auto serial no.
I Hope these Article help u .....
ReplyDeleteif any type of query then ask....
Bye Bye Have A Good Day .........