How to find third or nth maximum salary from salar

问题:

How to find third or nth maximum salary from salary table(EmpID,EmpName,EmpSalary) in Optimized way?

回答1:

Use ROW_NUMBER(if you want a single) or DENSE_RANK(for all related rows):

WITH CTE AS
(
    SELECT EmpID, EmpName, EmpSalary,
           RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
    FROM dbo.Salary
)
SELECT EmpID, EmpName, EmpSalary
FROM CTE
WHERE RN = @NthRow

回答2:

Row Number :

SELECT Salary,EmpName
FROM
  (
   SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum
   FROM EMPLOYEE
   ) As A
WHERE A.RowNum IN (2,3)

Sub Query :

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
               SELECT COUNT(DISTINCT(Emp2.Salary))
               FROM Employee Emp2
               WHERE Emp2.Salary > Emp1.Salary
               )

Top Keyword :

SELECT TOP 1 salary
FROM (
      SELECT DISTINCT TOP n salary
      FROM employee
      ORDER BY salary DESC
      ) a
ORDER BY salary

回答3:

Try this

SELECT TOP 1 salary FROM (
   SELECT TOP 3 salary 
   FROM employees 
   ORDER BY salary DESC) AS emp 
ORDER BY salary ASC

For 3 you can replace any value...

回答4:

If you want optimize way means use TOP Keyword, So the nth max and min salaries query as follows but the queries look like a tricky as in reverse order by using aggregate function names:

N maximum salary:

SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary DESC) 

for Ex: 3 maximum salary:

SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary DESC) 

N minimum salary:

SELECT MAX(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary ASC)

for Ex: 3 minimum salary:

SELECT MAX(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary ASC)

回答5:

Too simple if you use the sub query!

SELECT MIN(EmpSalary) from (
SELECT EmpSalary from Employee ORDER BY EmpSalary DESC LIMIT 3
);

You can here just change the nth value after the LIMIT constraint.

Here in this the Sub query Select EmpSalary from Employee Order by EmpSalary DESC Limit 3; would return the top 3 salaries of the Employees. Out of the result we will choose the Minimum salary using MIN command to get the 3rd TOP salary of the employee.

回答6:

Replace N with your Max Number

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

Explanation

The query above can be quite confusing if you have not seen anything like it before – the inner query is what’s called a correlated sub-query because the inner query (the subquery) uses a value from the outer query (in this case the Emp1 table) in it’s WHERE clause.

And Source

回答7:

Third or nth maximum salary from salary table without using subquery

select salary from salary
   ORDER   BY salary DESC
   OFFSET  N-1 ROWS
   FETCH NEXT 1 ROWS ONLY

For 3rd highest salary put 2 in place of N-1

回答8:

SELECT Salary,EmpName
FROM
(
SELECT Salary,EmpName,DENSE_RANK() OVER(ORDER BY Salary DESC) Rno from EMPLOYEE
) tbl
WHERE Rno=3

回答9:

Refer following query for getting nth highest salary. By this way you get nth highest salary in MYSQL. If you want get nth lowest salary only you need to replace DESC by ASC in the query.

回答10:

Method 1:

SELECT TOP 1 salary FROM (
SELECT TOP 3 salary 
 FROM employees 
  ORDER BY salary DESC) AS emp 
 ORDER BY salary ASC

Method 2:

  Select EmpName,salary from
  (
    select EmpName,salary ,Row_Number() over(order by salary desc) as rowid      
     from EmpTbl)
   as a where rowid=3

回答11:

In 2008 we can use ROW_NUMBER() OVER (ORDER BY EmpSalary DESC) to get a rank without ties that we can use.

For example we can get the 8th highest this way, or change @N to something else or use it as a parameter in a function if you like.

DECLARE @N INT = 8;
WITH rankedSalaries AS
(
SELECT
EmpID
,EmpName
,EmpSalary,
,RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
FROM salary
)
SELECT
EmpID
,EmpName
,EmpSalary
FROM rankedSalaries
WHERE RN = @N;

In SQL Server 2012 as you might know this is performed more intuitively using LAG().

回答12:

SELECT EmpSalary 
FROM salary_table 
GROUP BY EmpSalary 
ORDER BY EmpSalary DESC LIMIT n-1, 1;

回答13:

declare @maxNthSal as nvarchar(20)
SELECT TOP 3 @maxNthSal=GRN_NAME FROM GRN_HDR   ORDER BY GRN_NAME DESC
print @maxNthSal

回答14:

This is one of the popular question in any SQL interview. I am going to write down different queries to find out the nth highest value of a column.

I have created a table named “Emloyee” by running the below script.

CREATE TABLE Employee([Eid] [float] NULL,[Ename] [nvarchar](255) NULL,[Basic_Sal] [float] NULL)

Now I am going to insert 8 rows into this table by running below insert statement.

insert into Employee values(1,'Neeraj',45000)
insert into Employee values(2,'Ankit',5000)
insert into Employee values(3,'Akshay',6000)
insert into Employee values(4,'Ramesh',7600)
insert into Employee values(5,'Vikas',4000)
insert into Employee values(7,'Neha',8500)
insert into Employee values(8,'Shivika',4500)
insert into Employee values(9,'Tarun',9500)

Now we will find out 3rd highest Basic_sal from the above table using different queries. I have run the below query in management studio and below is the result.

select * from Employee order by Basic_Sal desc

We can see in the above image that 3rd highest Basic Salary would be 8500. I am writing 3 different ways of doing the same. By running all three mentioned below queries we will get same result i.e. 8500.

First Way: - Using row number function

select Ename,Basic_sal
from(
            select Ename,Basic_Sal,ROW_NUMBER() over (order by Basic_Sal desc) as rowid from Employee
      )A
where rowid=2

回答15:

Select TOP 1 Salary as '3rd Highest Salary' from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC) a ORDER BY Salary ASC;

I am showing 3rd highest salary

回答16:

SELECT MIN(COLUMN_NAME)
FROM   (
           SELECT DISTINCT TOP 3     COLUMN_NAME
           FROM   TABLE_NAME
           ORDER BY
                  COLUMN_NAME        DESC
       ) AS 'COLUMN_NAME'

回答17:

--nth highest salary

select * 
from (select lstName, salary, row_number() over( order by salary desc) as rn 
      from employee) tmp
where rn = 2

--(nth -1) highest salary

select * 
from employee e1
where 1 = (select count(distinct salary)  
           from employee e2
           where e2.Salary > e1.Salary )

回答18:

Optimized way: Instead of subquery just use limit.

select distinct salary from employee order by salary desc limit nth, 1;

See limit syntax here http://www.mysqltutorial.org/mysql-limit.aspx

回答19:

To get third highest value from table

SELECT * FROM tableName ORDER BY columnName DESC LIMIT 2, 1

回答20:

By subquery:

SELECT salary from
(SELECT rownum ID, EmpSalary salary from
(SELECT DISTINCT EmpSalary from salary_table order by EmpSalary DESC)
where ID = nth)

回答21:

Try this Query

SELECT DISTINCT salary
FROM emp E WHERE
&no =(SELECT COUNT(DISTINCT salary) 
FROM emp WHERE E.salary <= salary)

Put n= which value you want

回答22:

set @n = $n

SELECT a.* FROM ( select a.* , @rn = @rn+1  from EMPLOYEE order by a.EmpSalary desc ) As a  where rn = @n

回答23:

MySQL tested solution, assume N = 4:

select min(CustomerID) from (SELECT distinct CustomerID FROM Customers order by CustomerID desc LIMIT 4) as A;

Another example:

select min(country) from (SELECT distinct country FROM Customers order by country desc limit 3);

回答24:

Try this code :-

SELECT *
   FROM one one1
   WHERE ( n ) = ( SELECT COUNT( one2.salary )
                   FROM one one2
                   WHERE one2.salary >= one1.salary
                 )

回答25:

To query the nth highest bonus, say n=10, using AdventureWorks2012, Try Following code

USE AdventureWorks2012; 
GO

SELECT * FROM Sales.SalesPerson;
GO

DECLARE @grade INT;
SET @grade = 10;
SELECT MIN(Bonus)
FROM (SELECT TOP (@grade) Bonus FROM (SELECT DISTINCT(Bonus) FROM Sales.SalesPerson) AS a ORDER BY Bonus DESC) AS g

回答26:

another way to find last highest data based on date

SELECT A.JID,A.EntryDate,RefundDate,Comments,Refund, ActionBy FROM (
(select JID, Max(EntryDate) AS EntryDate from refundrequested GROUP BY JID) A 
Inner JOIN (SELECT JID,ENTRYDATE,refundDate,Comments,refund,ActionBy from refundrequested) B 
ON A.JID=B.JID AND A.EntryDate = B.EntryDate) 

回答27:

//you can find n' th salary from table.if you want to retrive 2nd highest salary then put n=2,if 3rd hs then out n=3 as so on..

SELECT *  FROM tablename t1 
WHERE (N-1) = (SELECT COUNT(DISTINCT(t2.Salary)) 
 FROM tablename t2
WHERE t2.Salary > t1.Salary)

回答28:

You can try this:

select top(1) EXPORT_NO
from DC_HDR 
order by CASE when  (ROW_NUMBER() over(order by EXPORT_NO desc))=3 then EXPORT_NO else 0 end desc

回答29:

select min(salary) 
from (select salary 
      from employee 
      where rownum < n+1 
      order by salary desc);

回答30:

Showing all 3rd highest salary:

select * from emp where sal=
(SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 3,1) ;

Showing only 3rd highest salary:

SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 3,1