How to find third or nth maximum salary from salary table(EmpID, EmpName, EmpSalary)
in optimized way?
This question is related to
sql
sql-server
sql-server-2008
declare @nHighestSalary as int
set @nHighestSalary = 3
SELECT TOP 1 salary FROM (
SELECT TOP @nHighestSalary salary
FROM employees
ORDER BY salary DESC) AS emp
ORDER BY salary ASC
select a.GRN_NAME
from GRN_HDR a,GRN_HDR b
where a.GRN_NAME<=b.GRN_NAME
group by a.GRN_NAME
having count(a.GRN_NAME)=3
For nth highest salary..
SELECT DISTINCT Salary
FROM EMP E WHERE
n =(SELECT COUNT(DISTINCT SALARY)
FROM EMP WHERE E.SALARY <= SALARY)
n being the highest value you want, i.e 2,3 etc
Try this code :-
SELECT *
FROM one one1
WHERE ( n ) = ( SELECT COUNT( one2.salary )
FROM one one2
WHERE one2.salary >= one1.salary
)
Try this one...
SELECT MAX(salary) FROM employee WHERE salary NOT IN (SELECT * FROM employee ORDERBY salary DESC LIMIT n-1)
SELECT TOP 1 salary FROM (
SELECT TOP 3 salary
FROM employees
Group By salary ORDER BY salary DESC ) AS emp
ORDER BY salary ASC
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
To get third highest value from table
SELECT * FROM tableName ORDER BY columnName DESC LIMIT 2, 1
set @n = $n
SELECT a.* FROM ( select a.* , @rn = @rn+1 from EMPLOYEE order by a.EmpSalary desc ) As a where rn = @n
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
--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 )
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
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
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);
Find Nth highest salary from a table. Here is a way to do this task using dense_rank() function.
select linkorder from u_links
select max(linkorder) from u_links
select max(linkorder) from u_links where linkorder < (select max(linkorder) from u_links)
select top 1 linkorder
from ( select distinct top 2 linkorder from u_links order by linkorder desc) tmp
order by linkorder asc
DENSE_RANK : 1. DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. 2. This function accepts arguments as any numeric data type and returns NUMBER. 3. As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause. 4. In the above query the rank is returned based on sal of the employee table. In case of tie, it assigns equal rank to all the rows.
WITH result AS (
SELECT linkorder ,DENSE_RANK() OVER ( ORDER BY linkorder DESC ) AS DanseRank
FROM u_links )
SELECT TOP 1 linkorder FROM result WHERE DanseRank = 5
In SQL Server 2012+, OFFSET...FETCH would be an efficient way to achieve this:
DECLARE @N AS INT;
SET @N = 3;
SELECT
EmpSalary
FROM
dbo.Salary
ORDER BY
EmpSalary DESC
OFFSET (@N-1) ROWS
FETCH NEXT 1 ROWS ONLY
finding Nth max value using CTE and FIRST_VALUE function. -- 5th max salary
;WITH CTE_NTH_SAL AS
(SELECT FIRST_VALUE(ESAL) OVER(ORDER BY ESAL DESC) AS ESAL,
1 AS ID
FROM EMPLOYEE
UNION ALL
SELECT FIRST_VALUE(EMP.ESAL) OVER(ORDER BY EMP.ESAL DESC) AS ESAL,
ID
FROM EMPLOYEE EMP,
(SELECT ESAL,
ID+1 AS ID
FROM CTE_NTH_SAL) CTE_NTH_SAL
WHERE EMP.ESAL<CTE_NTH_SAL.ESAL
AND CTE_NTH_SAL.ID<=5 )
SELECT DISTINCT ESAL
FROM CTE_NTH_SAL
WHERE ID=5
for sample result set and more ways clickhere
By subquery:
SELECT salary from
(SELECT rownum ID, EmpSalary salary from
(SELECT DISTINCT EmpSalary from salary_table order by EmpSalary DESC)
where ID = nth)
SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
SELECT TOP 1 salary
FROM employee
WHERE salary IN (SELECT DISTINCT TOP 3 salary
FROM employee
ORDER BY salary DESC)
ORDER BY salary ASC
SELECT MIN(COLUMN_NAME)
FROM (
SELECT DISTINCT TOP 3 COLUMN_NAME
FROM TABLE_NAME
ORDER BY
COLUMN_NAME DESC
) AS 'COLUMN_NAME'
//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)
declare @maxNthSal as nvarchar(20)
SELECT TOP 3 @maxNthSal=GRN_NAME FROM GRN_HDR ORDER BY GRN_NAME DESC
print @maxNthSal
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
select
Min(salary)
from ( select salary from employees order by salary desc) t
where rownum<=3;
For 2nd highest salary,Change 3 to 2 in above query and for Nth highest salary to N where N = 1,2,3,4....
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
SELECT TOP 1 salary FROM ( SELECT TOP n salary FROM employees ORDER BY salary DESC Group By salary ) AS emp ORDER BY salary ASC
(where n for nth maximum salary)
select max(sal)
from emp
where sal > (
select max(sal)
from emp
where sal > (select max(sal) from emp)
);
Optimized way,
;With CTE as
(
select salary
,RANK(order by salary desc) as Rnk
from table
)
select * from CTE where Rnk = @yourVariable
Second Opinion,
Declare @yourVariable int = 2
Select top 1 * from
(
select distinct top(@yourVariable) salary
from Employees
order by salary desc
)as a
order by a.salary asc
select * from Employees
order by salary desc
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
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.
NOTE: Please replace OFFSET 3 in Query with ANY Nth integer number
SELECT EmpName,EmpSalary
FROM SALARY
ORDER BY EmpSalary DESC
OFFSET 3 ROWS
FETCH NEXT 1 ROWS ONLY
Description
FETCH NEXT 1 ROWS ONLY
return only 1 row
OFFSET 3 ROWS
exclude first 3 records Here you can you any integer number
This is mysql query for fetching the nth highest salary from the table emp. We use "LIMIT" here as an alternative to "TOP" and the parameter (n-1) shows after which row to start from and the second parameter shows how many rows to show starting from the (n-1)th row. "LIMIT" accepts single parameter also which represents the number of rows to show starting from 0th(or 1st).
select distinct * from emp order by sal desc limit (n-1),1;
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)
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
Subqueries always take more time:
use below query to get the any highest and lowest data:
Highest Data: select *from business order by id desc limit 3,1;
Lowest data: select *from business order by id asc limit 3,1;
Can use N in the place of 3 to get nth data.
To find the 5th highest Salary:
Declare @N INT = 5
SELECT Salary FROM Employee
ORDER BY Salary DESC OFFSET @N - 1 ROW
select min(salary)
from (select salary
from employee
where rownum < n+1
order by salary desc);
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().
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
SELECT * FROM (select distinct Salary from Customers order by salary DESC) limit 4,1;
Limit 4,1 means leave first 4 rows and then select the next one.
Limit and rownumber depends on the platform you are using.
Try this,it will work.
SELECT EmpSalary
FROM salary_table
GROUP BY EmpSalary
ORDER BY EmpSalary DESC LIMIT n-1, 1;
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
select * from employee order by salary desc;
+------+------+------+-----------+
| id | name | age | salary |
+------+------+------+-----------+
| 5 | AJ | 20 | 100000.00 |
| 4 | Ajay | 25 | 80000.00 |
| 2 | ASM | 28 | 50000.00 |
| 3 | AM | 22 | 50000.00 |
| 1 | AJ | 24 | 30000.00 |
| 6 | Riu | 20 | 20000.00 |
+------+------+------+-----------+
select distinct salary from employee e1 where (n) = (select count( distinct(salary) ) from employee e2 where e1.salary<=e2.salary);
Replace n with the nth highest salary as number.
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
For n-th highest value
select min(salary)
from (select salary from(select salary from employee order by salary desc)where rownum<=n);
Hi One more example to write this using common table expression:
with cte as
(SELECT TOP 3 salary
FROM salary
ORDER BY salary DESC)
select top 1 salary from cte
with cte as
(select e.Name,s.salary ,DENSE_RANK ()over ( order by salary desc) sal_rank
from salary s left join employee e
on s.EmpID=e.EmpID)
select Name,salary from cte where sal_rank=3
SELECT Salary,EmpName
FROM
(
SELECT Salary,EmpName,DENSE_RANK() OVER(ORDER BY Salary DESC) Rno from EMPLOYEE
) tbl
WHERE Rno=3
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)
Just change the inner query value: E.g Select Top (2)* from Student_Info order by ClassID desc
Use for both problem:
Select Top (1)* from
(
Select Top (1)* from Student_Info order by ClassID desc
) as wsdwe
order by ClassID
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...
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.
select * from emp x where &no=(select count(*) from emp y where y.sal>=x.sal);
this will give take input from user and then will tell the nth maximum number.I have taken example of emp table in oracle and to display nth maximum salaried employee info
Output Enter value for no: 5
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 3000 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
Enter value for no: 14
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
Source: Stackoverflow.com