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 blog I am always very eager to read comments of readers. Some of regular readers are industry leaders and and their comments always teach us all something new. One of them is Nicholas Paldino [.NET/C# MVP]. He has always provided valuable solution and comments to this blog. His recent comment about finding Nth Highest Record is quite an interesting.
USE AdventureWorks
GO
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 = 4
Reference : Pinal Dave (https://blog.sqlauthority.com) , Nicholas Paldino [.NET/C# MVP]
24 Comments. Leave new
Yes, row_number() is quite useful in these cases, and during pagination
but it is a performance drag as well, especially for pagination
imagine assigning row # to millions of records, and only need record 50~100 for page 2
Also see what you can do with row_number() function
And many methods to find Nth Maximum value
But if the records are duplicated i dont think it will return the correct result ??
Very nice approach Nicholas.
I used the same query to gain little bit performance when we have to have the nth value out of very long list. This can give some performance boost. The inner query will give only the top results from which the top most row will be displayed
USE AdventureWorks
GO
declare @nthVal int
set @nthVal=4–the required nth value
SELECT top 1 t.*
FROM
(
SELECT top (@nthVal) e1.*,row_number() OVER (ORDER BY e1.Rate DESC) AS _Rank
FROM HumanResources.EmployeePayHistory AS e1
) AS t
order by t._Rank desc
owsem man.. this site has helped me very weill with my interview.
keep it up
@ Jerry Hung
Can you give some more information on how row_number() will reduce performance?
Thanks in advance.
how do we find positions of the record from a table using SQL SERVER 2000? for example
table name : employee
EMP_NAME AGE SALARY
———————————–
aaa 30 2000.00
abc 20 5000.00
bbb 40 3000.00
bbb 60 3000.00
ccc 50 4000.00
xyz 25 1000.00
there is no primary key in this table.
In this table i want to find out 3rd record. i.e.
bbb 40 3000.00
We can write this query like this also
Select * from table name order by desc column name lmit1,1
Hi All,
I want to do pagination using SQL, i having MS SQL 2000. It does not support row_number(). How can i do pagination !!
Please Help !!
Thanks on Adv.
Rocky R
Hi All,
I want to Pagination using SQL. I having db Ms Sql 2000.
Please help on this front.
Thanks in Adv.
Regards
Rocky R
I just need to find the highest value row in a table. How do I do that? I assume it is going to be a simpler command.
select max(col) from your_table
Good Use of RANK() function.
I want to Pagination using SQL. I having db Ms Sql 2000.
Please help on this front.
Thanks in Adv.
How To Find a Record 3rd From bottom In Database?
Can anyone help me?
There is no concept of TOP or BOTTOM record in relationla database until you define it
> Ajith
>
> But if the records are duplicated i dont think it will return > the correct result ??
Use DENSE_RANK() for this situation instead of ROW_NUMBER()
Am I too late?
Try this..
SELECT TOP 1 ID,Name
FROM
(SELECT TOP 6
ID, Name
FROM Table
ORDER BY ID ASC) as top6
ORDER BY ID Desc
Hi All,
Is it possible that……..
Find Nth Highest Record from Database Table without using “TOP” and “ORDER BY”???
Refer this to know more such methods
[last reply was a mistake]
@Dinesh
Using ROW_NUMBER() as in the article does not use the normal ORDER BY, that ORDER BY is part of the ROW_NUMBER syntax.
There is a way to do it without even that, but it is not very efficient. Bascially, use MAX() and WHERE <.
For example, to find the third highest id:
SELECT MAX(id) FROM mytab WHERE id < (SELECT MAX(id) FROM mytab WHERE id < (SELECT MAX(id) FROM mytab))
This can be done with a join as well.
@ Brian Tkatch
thank you so much for the info
you’ve help me a lot…
Thanks Sir .