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
Hi,
Can we find out the first inserted row from a table?
Regards
Manohar K Patidar Jamner (M. P. )
There is no concept of first/last row until you define it
table_a table_b
salary salary
_____ _____
1000 2000
3000 4000
I want a T-sql query to get “4000” as answer
select max(salary) as salary from
(
select salary from table_a
union all
select salary from table_b
) as t
Hello Pinal Dave!!!
I am not able to understand why the following query gave me the correct answer
“SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)”
Can you explain me in step by step manner how it returned correct answer?
I will be extremely thankful to you if you can do it for me.
Refer method 3
It is equalent to this method
The idea is to generate serial no based on the value and compare it in the where clause
in that where clause, u should specify the column name only. Not a variable
Thanks a lot! This query works well.
How to import by query, as I have below command for table import
exp rtrs_fd/rtrs_fd@reuters file=exp.dmp log=exp.log tables=
issue_counterparty_100 consistent=y
statistics=none buffer=10000000
imp REUT_ONSHORE/REUT_ONSHORE@REUT_US_105 file=C:\IMP\exp.dmp
log=C:\IMP\exp.log tables= issue_source_18_mar09 ignore=y commit=y
buffer=10000000
hi !! Pinal Dave,
Thanks for the query.. it is really helpful….
but can u explain in steps that how the above query actually works ?
becoz i dont know the execution flow of this query…
hi !! Pinal Dave,
Thanks for the query.. it is really helpful….
but can u explain in steps that how the above query actually works ?
becoz i dont know the execution flow of this query
There are N number of methods that includes aggregation, row_number(), top etc to get Nth Maximum value
Refer this to know more
i use sql 2008 and this query gives error .the error shows “Invalid column name ‘N’.” what is error pls tell me
hi biswajit,
here N stands for level means which salary level u want to get.
like n=1,2,3,4,5,6……………n,
try it
bye
You should relplace N with a specific value
If you use 5, it means 5th maximum
Hi,
I want to get top 5 salary from employee table without using sql function top and min or max.
Thanks
Why do you want to do this?
The following link has some methods that dont use TOP, min or max
Refer this
Hi Saurabh,
You can use ROWCOUNT property to fetch only five records from query.
SET ROWCOUNT 5;
select * from Employees
ORDER BY Salary DESC
Here I specify ROWCOUNT property to 5, so it will select only 5 records based on query.
By this way you can get top 5 employees having high salary.
Thanks,
Tejas
SQLYoga.com
Note that ROWCOUNT will not be supported (for INSERT, UPDATE and DELETE) in future release of the SQL Server. You should use TOP
hi pinal,
This is dhirendra!
i am quite satisfied by your query.
can u pls breifly give the steps how the query is executed.
waiting 4 ur reply.
thanks
It basically generate serial number based on the salary
It is generated in ascending order of the salary and you use use it in where clause to get Nth maximum value
Thanks for your query.
Regards,
Sambit
Hello Pinal Dave,
It works for me.Thanks.
Regards,
Minakshi.
Hi,
I am looking to retrieve similar data but in a different way but cant quite get it right.
What i have is a customer table and a Purchases table, and i want to create a view that will give me:
Customer, MostRecenetPurchase, SecondMostRecentPurchase,…
Please help!
Many Thanks
Scott
Hi, can you write a query to find the 5th Highest salary of a table without using inner / sub query.?
Thanks, Kishan
Make use of this post
Thankq sir
thanq sir
Thanks a lot,
I have a query, if I replace “N” with 1, then actually it return lowest and highest one salary.
SELECT *
FROM Employee E1
WHERE (1-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)
Why?
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.