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)
GOReference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
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).
[...] 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.
Hi all,
i need to write a Query to find the nth record from a table. Please can any help me out?
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.