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)
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
[...] 12, 2008 by pinaldave I have previously written SQL SERVER – 2005 – Find Nth Highest Record from Database Table where I have shown query to find 4th highest record from database table. Everytime when I write [...]
Hi! Pinal,
What is Embedded SQL Query?
Many methods
http://sqlblogcasts.com/blog/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
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
http://beyondrelational.com/ask/madhivanan/questions/784/find-nth-maximum-value.aspx
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
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)
[...] I have re-wrote the same article here with example of SQL Server 2005 Database AdventureWorks : SQL SERVER – 2005 – Find Nth Highest Record from Database … [...]
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
http://beyondrelational.com/ask/madhivanan/questions/784/find-nth-maximum-value.aspx
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 ?
How can I calculate monthly income of the employees in the adventure works
[...] Find Nth Highest Record from Database Table It is very easy to find the top and bottom records but it is very difficult to find North Record in SQL Server. This blog post discusses how one can do the same. [...]