SQL SERVER – Find Nth Highest Record from Database Table

I had previously written SQL SERVER – Query to Retrieve the Nth Maximum value. I just received an email that if I can write this using AdventureWorks database as it is a default sample database for SQL Server 2005 and the user can run the query against it and understand it better. Let us see how we can find highest record from database.

SQL SERVER - Find Nth Highest Record from Database Table nthhigh-800x291

Here is query to find 4th Highest Record from Database Table.

USE AdventureWorks;
GO
SELECT *
FROM HumanResources.EmployeePayHistory E1
WHERE (4-1) = (SELECT COUNT(DISTINCT(E2.Rate))
FROM HumanResources.EmployeePayHistory E2
WHERE E2.Rate > E1.Rate)
GO

Here is the result set of the above query which retrieves 4th highest record from the database table.

SQL SERVER - Find Nth Highest Record from Database Table 4thhighest

In above query where you see (4-1), you can replace the 4 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.

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)

, ,
Previous Post
SQLAuthority News – 500th Post – An Interesting Journey with SQL Server
Next Post
SQLAuthority News – Authors Most Visited Article on Blog

Related Posts

32 Comments. Leave new

  • Nicholas Paldino [.NET/C# MVP]
    March 11, 2008 11:57 pm

    If you are using SQL Server 2005, you are better off using the ranking function ROW_NUMBER, and then querying for the appropriate value:

    select
    t.*
    from
    (
    select
    e1.*,
    row_number() over (order by e1.Rate desc) as _Rank
    from
    HumanResources.EmployeePayHistory as e1
    ) as t
    where
    t._Rank = 3

    Replace 3 with whatever rank you want (this will give you the record with the third highest rate).

    Reply
  • Hi! Pinal,

    What is Embedded SQL Query?

    Reply
  • Many methods

    Reply
  • How to get nth record(its not highest or lowest) from a table. I am working on SQL SERVER 2005.

    Reply
  • Hi all,
    i need to write a Query to find the nth record from a table. Please can any help me out?

    Reply
    • Refer this post

      Reply
    • select * from tbl_EmployeeDetail as e1 where (2-1)=(select count(e2.EmployeeID) from tbl_EmployeeDetail as e2 where e1.EmployeeID<e2.EmployeeID)

      if we want to find 2nd no. record then this query is used..

      Reply
  • Hi alll,

    use this query for any record you want to find in the table.

    SELECT TOP A salary FROM (SELECT TOP (n) salary FROM employee ORDER BY salary DESC)
    AS E ORDER BY salary ASC

    top(n) give the total no of records

    a desire top value

    another way

    select t.* from (select row_number() over(order by salary) as row_id, salary from sal)
    t where t.row_id = 24

    give the which nth record you want

    Reply
  • Hi nagesh,

    Thank you for trying this. Can u pls help me out if their is any other query, as the result is not as expected.

    This is not what i need.

    Suppose i have a table with following data

    EmpNo Empname sal

    1 X 100

    2 y 300

    3 a 50

    4 b 200

    5 c 500

    6 d 250

    Here i want 3rd record i.e 3 a 50, using the sal column.

    If i use ur query i will get 4 b 200 or if the order is changed u may get 6 d 250.
    In which the result was not as i expected.
    I just want the nth record it may or may not have duplicates

    Reply
  • hi Vishwanath,

    can you try this, where i’m trying to find the third highest salary

    select top 1 *
    from (select top 3 *
    from emp_table
    order by salary desc)
    order by salary

    Reply
    • yes..
      different ways to find 2nd highest max Amount
      —-1st Query——
      SELECT MIN(Amount)
      FROM
      (
      select Distinct top 2 Amount
      from tbl_Report_Detail
      Order by Amount Desc
      )A

      —–2nd Query————
      SELECT DISTINCT MAX(Amount)
      FROM tbl_Report_Detail
      WHERE Amount<(select MAX(Amount)FROM tbl_Report_Detail)

      —–3rd Query—–
      SELECT DISTINCT TOP 1 Amount
      FROM
      ( SELECT DISTINCT TOP 2 Amount
      FROM tbl_Report_Detail
      ORDER BY Amount DESC
      ) AS Amt
      ORDER BY Amount ASC
      ———–4th Query N- highest Max Salary——————

      SELECT DISTINCT Amount
      FROM
      (
      SELECT Amount, ROW_NUMBER()OVER (Order By Amount Desc)as ID
      FROM tbl_Report_Detail a
      )t
      WHERE ID=3
      ORDER BY Amount DESC

      Reply
  • hi ,
    this is vidyullatha. i have a dought in sql how to get the nth highest salary from emp table.can i get using sql plz tell me the answer.

    Reply
    • Hi Vidyullatha,

      Use this
      SELECT * FROM Employee e1 WHERE (N-1) = (SELECT COUNT(DISTINCT(p2.salary))
      FROM Employee e2 WHERE e2.salary > e1.salary)

      Reply
  • Hi Pinal,

    I would like to know how this query is executes .i am unable to understand how this query is joining E1 , E2 .
    as i knows first inner query will be fired but it will not have any info about E1 because it is in outer query.
    I will prefer
    SELECT *
    FROM HumanResources.EmployeePayHistory E1
    WHERE (4-1) = (SELECT COUNT(DISTINCT(E2.Rate))
    FROM HumanResources.EmployeePayHistory E2
    WHERE E2.Rate > E1.Rate)

    this query to get n th record because it is less depent on DB functions it is using general function .

    Reply
  • Please send how to code to find the highest record from a field while the number is added with any string.
    for example “BIS001,BIS002,BIS009,BIS010,BIS003″…i need ans hear is BIS010

    Reply
  • Ashish Gilhotra
    January 29, 2010 5:55 pm

    @murugaveni.K

    Here we go

    DECLARE @string varchar(500)
    DECLARE @Xml XML
    SET @string=’BIS001,BIS002,BIS009,BIS010,BIS003′
    SET @xml = ‘
    ‘ + REPLACE(@string, ‘,’, ”) + ” +

    SELECT top 1 x.v.value(‘.’,’VARCHAR(100)’)
    FROM @xml.nodes(‘/IDs/ID’) x(v)
    order by x.v.value(‘.’,’VARCHAR(100)’) desc

    Reply
  • Hi,
    How can i get second highest and second lowest salary in a single query..

    Reply
  • Hi All,

    Request you to all please find below mentioned query, if i am wrong then feel free to let me know… while i have tried this query and it’s giving rite output….

    it will show sixth highest salary…

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

    Reply
  • S SaravanaKumar
    March 18, 2011 10:13 am

    Pinal dave, i have Nth highest salary query, kindly explain how its working
    DECLARE @N INT = 1
    SELECT * FROM EmployeeDetail A WHERE (@N = (SELECT COUNT( DISTINCT(B.Salary))
    FROM EmployeeDetail B WHERE B.Salary >= A.Salary))

    Reply
    • The subquery assign serial no based on the descending order of the salary and if the serial no is 5, it means it is the 5th maximum salary

      Reply
  • tariquehassan hassan
    June 27, 2011 12:04 pm

    SELECT TOP (100) PERCENT A.hist_dt, A.acc_type_cd, A.acc_no, A.current_balance, B.hist_dt AS Expr1
    FROM ecslcall.dbo.investor_nav_hist AS A INNER JOIN
    (SELECT hist_dt, acc_type_cd, acc_no, current_balance, div_income, deposit
    FROM ecslcall.dbo.investor_nav_hist) AS B ON A.acc_no = B.acc_no AND A.hist_dt < B.hist_dt
    WHERE (A.acc_no = 308) AND (B.hist_dt = CONVERT(DATETIME, '2011-05-22 00:00:00', 102))
    ORDER BY A.hist_dt DESC

    Reply
  • tariquehassan hassan
    June 27, 2011 12:05 pm

    Try This…

    SELECT TOP (1) PERCENT A.hist_dt, A.acc_type_cd, A.acc_no, A.current_balance, B.hist_dt AS Expr1
    FROM ecslcall.dbo.investor_nav_hist AS A INNER JOIN
    (SELECT hist_dt, acc_type_cd, acc_no, current_balance, div_income, deposit
    FROM ecslcall.dbo.investor_nav_hist) AS B ON A.acc_no = B.acc_no AND A.hist_dt < B.hist_dt
    WHERE (A.acc_no = 308) AND (B.hist_dt = CONVERT(DATETIME, '2011-05-22 00:00:00', 102))
    ORDER BY A.hist_dt DESC

    Reply
  • can any one explain the query to find the N th max value

    Reply
  • Deepak A. Patil
    December 1, 2011 5:31 pm

    I want to have strictly only Five Records as output of Employees out of 100 or (out of n no of records) with highest salary arranged in asc or dsec order
    with out using Top()

    Reply
  • SELECT *
    FROM HumanResources.EmployeePayHistory E1
    WHERE (4-1) = (SELECT COUNT(DISTINCT(E2.Rate))
    FROM HumanResources.EmployeePayHistory E2
    WHERE E2.Rate > E1.Rate)

    Could anybody explain what does (4-1) mean in the where clause ?

    Reply

Leave a Reply