11
Reply

N th Maximum and Minimum salary in SQL server

Rajesh Pathakoti

Rajesh Pathakoti
11y
2.2k
0
Reply
    praveesh srivastava
    select top 1 * from Employee where salary in (select distinct top N salary from Employee order by salary desc)order by salary asc
    praveesh srivastava
    11y
    1
    Suresh kumar
    to find nth max salary:select * from salarytable s where 4=(select count(salary) from salarytabls s1 where s.salary<=s1.salary);to find nth min salary:select * from salarytable s where 4=(select count(salary) from salarytabls s1 where s.salary>=s1.salary);note: in above query i retrive 4th max & min salary from table so i use the number 4 in that query..u change whatever u need.
    Suresh kumar
    11y
    1
    Divendra Ojha
    Simple need to use the concept of subquery like: SELECT TOP 1 FROM WHERE SALARY NOT IN (SELECT TOP N-1 FROM ORDER BY DESC) ORDER BY DESC;
    Divendra Ojha
    11y
    1
    Manju lata Yadav
    I agreed with rajesh and other methods are:- Nth Maximum salary:- Select * From Employee E1 Where (N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary > E1.Salary) or SELECT * FROM (SELECT salary,Dense_rank() OVER (ORDER BY Salary desc) AS Rank FROM employees) t1 WHERE Rank=NNth minimum salary:- Select * From Employee E1 Where (N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary
    Manju lata Yadav
    11y
    1
    Rajesh Pathakoti
    N th Maximum salary: select max(sal) from salary where sal in(select top N sal from salary order by sal desc)N th Minimum salary: select min(sal) from Salary where sal in(select top N sal from salary order by sal asc)
    Rajesh Pathakoti
    11y
    1
    Sanjeev Kumar
    The method used by Suresh is correct but don't forget to use keyword distinct otherwise it will not work in case when more than one employee have same salary. select * from salarytable s where 4=(select count(DISTINCT salary) from salarytabls s1 where s.salary<=s1.salary);
    Sanjeev Kumar
    11y
    0
    Munesh Sharma
    get important SQL queries http://dotnet-munesh.blogspot.in/2013/12/important-sql-query.html
    Munesh Sharma
    11y
    0
    Kuldeep Patel
    Use SubQuery : N th Maximum salary: select max(salary) from salaryTable where salary in(select top N salary from salaryTable order by salary desc)N th Minimum salary: select min(salary) from SalaryTable where salary in(select top N salary from salaryTable order by salary asc)
    Kuldeep Patel
    11y
    0
    Suresh kumar
    to find nth max salary:select * from salarytable s where 4=(select count(salary) from salarytabls s1 where s.salary<=s1.salary);to find nth min salary:select * from salarytable s where 4=(select count(salary) from salarytabls s1 where s.salary>=s1.salary);note: in above query i retrive 4th max & min salary from table so i use the number 4 in that query..u change whatever u need.
    Suresh kumar
    11y
    0
    Divendra Ojha
    Simple need to use the concept of subquery like: SELECT TOP 1 FROM WHERE SALARY NOT IN (SELECT TOP N-1 FROM ORDER BY DESC) ORDER BY DESC;
    Divendra Ojha
    11y
    0
    hate decimal
    declare @tbl table (sal int, Sr int) insert into @tbl (sal,Sr) select sal,ROW_NUMBER() over (order by sal desc) R from table_1 declare @Total int = (Select COUNT(*) from @tbl) select * from @tbl where Sr in (NthMax, @Total-NthMin) --here NthMax,NthMin where number of highest and lowest sal order
    hate decimal
    11y
    0
Message