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.
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.
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)
32 Comments. Leave new
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).
Hi Nicholas,
if we have duplicate records, will we get the correct record for nth max?
-Ravi
Hi! Pinal,
What is Embedded SQL Query?
Many methods
How to get nth record(its not highest or lowest) from a table. I am working on SQL SERVER 2005.
I think you can use this:
Select Top N * From yourTable
N: the number of records you want to get
TOP without order by clause is meaningless
Hi all,
i need to write a Query to find the nth record from a table. Please can any help me out?
Refer this post
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..
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
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
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
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)
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 .
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
Just use substring(col,3,len(col))*1 in place of col in the above query
@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
Hi,
How can i get second highest and second lowest salary in a single query..
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
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))
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
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
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
can any one explain the query to find the N th max value
Refer these methods
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()
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 ?