Finding the nth Maximum Value means deciding how ties should count. Here, I rank distinct known salaries.
The correlated subquery counts distinct salaries greater than each candidate. Two employees with the same salary share one salary rank. Choosing the second employee after sorting asks a different question.
DECLARE @Employee TABLE (EmployeeID int PRIMARY KEY, Salary int NULL);
INSERT INTO @Employee VALUES (1,90000),(2,80000),(3,80000),(4,70000),(5,NULL);
DECLARE @N int = 2;
IF @N < 1 THROW 50000, 'N must be positive.', 1;
SELECT DISTINCT e.Salary
FROM @Employee AS e
WHERE e.Salary IS NOT NULL
AND @N - 1 =
(SELECT COUNT(DISTINCT e2.Salary)
FROM @Employee AS e2 WHERE e2.Salary > e.Salary);For this input, @N = 2 returns 80000 once. The two employees earning it don’t create another distinct rank. NULL salaries are excluded because they don’t represent a known amount.
A positive N beyond the number of distinct known salaries returns no row. The guard rejects zero and negative ranks.
To return every employee at that rank, select e.EmployeeID and e.Salary instead of DISTINCT e.Salary. The salary-only version intentionally returns one distinct amount.
Understand the logical expression
The subquery refers to the outer salary, which makes it correlated. That describes the SQL logic, not a promise that the optimizer physically executes the complete subquery once for every row. Check the actual plan when comparing implementations.
On current SQL Server versions, DENSE_RANK offers another direct way to express distinct salary ranks. The original later article remains linked below.
Related reading
A salary rank is not an employee position, it is a place among distinct salary values.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
63 Comments. Leave new
i want to find the second highest salary from table without ordering table and with no max or count function?
How can i do this???
please reply me…..
thank you..
from Kalpna Bindal
student of 3rd year in B.Tech.
select e.sal from emp e
where &n = (select count(distinct(b.sal)) from emp b
where e.sal <= b.sal);
very nice sir..makes a lot of help..thx.
Hi This query is working fine but whenever there is lacs of records in your table at that time it will take more time so as performance issue i think so my issue will work fine my solution is:
with result AS
(
select maths,RANK() over (order by maths desc) as row from tblResult group by maths
)
select * from result r inner join tblResult t on r.maths = t.Maths where ROW = 3
Here in this query write your table name instead of result , column name instead of maths and your number instead of 3
Thanks
Is this helpful? Please give reply.
Can somebody show me the execution row by row for the Nth max below:
CREATE TABLE Employee
(
EmpId int PRIMARY Key nonclustered,
Name VARCHAR(50),
MgrId int,
Salary int
);
sp_help employee
–drop table employee
INSERT INTO Employee
SELECT 1, ‘Mike’, 3, 100
UNION ALL
SELECT 2, ‘David’, 3, 200
UNION ALL
SELECT 3, ‘Roger’, NULL, 1000
UNION ALL
SELECT 4, ‘Marry’,2, 500
UNION ALL
SELECT 5, ‘Joseph’,2, 700
UNION ALL
SELECT 7, ‘Ben’,2, 50
GO
select * from Employee
order by salary desc
SELECT * FROM Employee E1
WHERE (5-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)
hi, i am getting time out error when i execute the following query
“select MAX(PartItemID) from PartItems”
my part item table having 6 lac records
is there any other way to get the max value for this table ..please help me if u know the solution
thank u
because it is taking too much time to execute it. when i execute it, it took 6 minutes and 3 seconds and your session expires that’s why it is giving time out error…..
Hello sir,
your query took 6 minutes and 3 seconds to fetch 9th maximum value..
I have a employee table there are only 2 fields Name and salary. i want to retrieve 1st record and last record how to retrieve the record please help me…