Replace Employee with your table name, and Salary with your column name. Where N is the level of Salary to be determined. Let us see a query to retrieve the Nth Maximum Value.
SELECT * FROM Employee E1 WHERE (N-1) = ( SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2 WHERE E2.Salary > E1.Salary)
In the above example, the inner query uses a value of the outer query in its filter condition, meaning; the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row.
In above query where you see (N-1), you can replace the N with any other number based on your need. For example, if you want to find the 10th highest record, you can just write (10-1) or just 9 there and it will bring back necessary record. It is a very easy query and requires one time proper understanding how it works.
Here is another relevant blog post which has working examples of AdventureWorks database: SQL SERVER – Find Nth Highest Record from Database Table
Let me know if you have any question in the comments section. I keep on sharing the various tips, I suggest you sign up for my newsletter http://go.sqlauthority.com
Reference : Pinal Dave (https://blog.sqlauthority.com)
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…