SQL SERVER – 2005 – Find Nth Highest Record from Database Table

I have previously written SQL SERVER – Query to Retrieve the Nth Maximum value. I just received email that if I can write this using AdventureWorks database as it is default sample database for SQL Server 2005 and user can run the query against it and understand it better.

Here is query to find Nth 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

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

About these ads

35 thoughts on “SQL SERVER – 2005 – Find Nth Highest Record from Database Table

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

  2. Pingback: SQL SERVER - 2005 - Find Nth Highest Record from Database Table - Using Ranking Function ROW_NUMBER Journey to SQL Authority with Pinal Dave

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

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

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

    • 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

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

    • Hi Vidyullatha,

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

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

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

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

  10. @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

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

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

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

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

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

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

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