SQL SERVER – Query to Retrieve the Nth Maximum Value

Replace Employee with your table name, and Salary with your column name. Where N is the level of Salary to be determined.

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.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

66 thoughts on “SQL SERVER – Query to Retrieve the Nth Maximum Value

  1. Hello,

    I want to find first 10 rows with max value from a table. Can you suggest me what should be query for this ?

    Thanking you in advance.

    Regds,
    Bhavesh

    Like

  2. Hi,

    Thanks for the perfect solution to find out the Nth record from sql server database.

    One request:
    how to get top 3 departments and bottom 3 departments

    waiting for ur reply

    with reagards
    sekar

    Like

  3. hi

    i want a query .dynamicale i new row should Added and it should display in Gui by using a Query.pleas provide me this query.

    Regards
    Srinath

    Like

  4. Pingback: SQL SERVER - 2005 - Find Nth Highest Record from Database Table Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER - Find Nth Highest Salary of Employee - Query to Retrieve the Nth Maximum value Journey to SQL Authority with Pinal Dave

  6. Hi executing the same query using Top keyword is much faster, when the number of records are comparatively too large. FYI (copied from the same site :) )

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

    Like

  7. I want to find first 10 rows with max value from a table.
    and

    how to get top 3 departments and bottom 3 departments
    Can you suggest me what should be queries for above two questions?

    Thanks
    krishna

    Like

  8. Hi All,
    I compared above said query and found this one performming like a Lamborghini Reventon as compared to the above proposed Ambassador, for table(id,name,salary) with 20K records:

    DECLARE @myNth INT
    SET @myNth=56
    SELECT TOP 1 SALARY
    FROM(
    SELECT TOP(@myNth) SALARY
    FROM MinMaxTable
    ORDER BY Salary DESC
    ) T
    ORDER BY Salary ASC

    So This one is the BEST !!!!

    Like

  9. Your query is correct if it came from student who has 5 minutes to solve this problem.

    But if it in real world application,
    Your query is very low performance.

    Like

  10. Hi

    following query will find out the nth max salary from employee table. just replace the number 5 with your number. Also it should check the duplicate value in salary field. I think Bala wants to know this.

    SELECT max(salary) FROM ( SELECT DISTINCT TOP 5 salary FROM employee order by salary) a

    Regards
    Satyabrata Paul

    Like

  11. 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.

    Like

  12. 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

    Like

  13. 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…

    Like

  14. 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

    Like

  15. 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

    Like

  16. 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

    Like

  17. 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

    Like

  18. 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?

    Like

  19. 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.

    Like

  20. 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.

    Like

  21. 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)

    Like

  22. 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

    Like

    • 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…..

      Like

  23. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

  24. 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…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s