How to find the n-th record ?
Hi,
Plz Help me !
If I want to find the n-th record ( order by one or some fields ), where
n is quite large ( such as 1,000,000 ).
How should I compose the SQL to get better performance?
Thanks !!
[240 byte] By [
jesvh] at [2007-11-19 23:17:43]
# 1 Re: How to find the n-th record ?
SELECT TOP 1 * FROM (SELECT TOP 2 * FROM TABLENAME ORDER BY COLUMNNAME DESC) A ORDER BY COLUMNNAME ASC This will show the second highest record. I am assuming that the Field COLUMNNAME is what you are actually refering as recordID.
# 2 Re: How to find the n-th record ?
The idea is to obtain a query with all lines numbered. so, in that moment you can access any record you want writing where Number=2000
Steps:
1. a functions that receives n and returns n=n+1
2. obtain a data set with order number:
select function(n) as Number, field1,field2 from MyTable order by... what you want
3. select x.Field1,x.field2 from (select function(n) as Number, field1,field2 from MyTable order by... what you want ) as x where x.Number=5000.
Hope it helped
# 3 Re: How to find the n-th record ?
Sorry , maybe I didn't descript it clearly.
what I want is the first record of the resultset of
SELECT TOP 1000000 * FROM table
WHERE .......
ORDER BY field DESC
I am not sure its performance is good or poor ......
so looking for other methods to compare with.
jesvh at 2007-11-9 13:45:51 >
# 4 Re: How to find the n-th record ?
If you are just going to look at the first record then why not do just this SELECT TOP 1 * FROM table
WHERE .......
ORDER BY field DESC
# 5 Re: How to find the n-th record ?
Hi all,
You don't say what db you're using. Nevertheless, adding an index on fields of ORDER BY clause can grow up performance.
# 6 Re: How to find the n-th record ?
Yes, it was a confusion about you requirements.
I agreed with Shuja Ali
# 7 Re: How to find the n-th record ?
Hi all,
If you have to set the ORDER BY condition for one column only, there is another solution:
select *
from MyTable
where field = (select max(field)
from MyTable)
This is standard SQL, and if field is primary key, or there's an index on field, probably this is the fastest query that solves your problem.
# 8 Re: How to find the n-th record ?
I said I want the n-th record (order by field), n is large, but not the total !!!
not the last record order by field or max(field) record !!
For example, if a database contains 400000000 records
and the query produces below records from R1 to Rn
( order by field ) , there n large than 1000000 and less than 4000000000
R1, R2, R3, ......... , R1000000, R1000001 ........ , Rn
I want to get the record - R1000000 , not Rn !!
jesvh at 2007-11-9 13:51:04 >
# 9 Re: How to find the n-th record ?
I said I want the n-th record (order by field), n is large, but not the total !!!
not the last record order by field or max(field) record !!
For example, if a database contains 400000000 records
and the query produces below records from R1 to Rn
( order by field ) , there n large than 1000000 and less than 4000000000
R1, R2, R3, ......... , R1000000, R1000001 ........ , Rn
I want to get the record - R1000000 , not Rn !!
Have you tried the solutions that were suggested?
# 10 Re: How to find the n-th record ?
Have you tried the solutions that were suggested?
Oh ... Sorry !!
Thank Mr. Shuja Ali
method 1 by Shuja Ali got the record Rn-1
method 2 by DanielaTm .... I don't know how to ....
method 3 by davide++ got the record with max(field)
the sample I post is not very clear, it should be as :
SELECT TOP 1 field FROM
(
SELECT TOP n field FROM table ORDER BY field ASC
)
ORDER BY field DESC
it will get the n-th record I want .
I don't know if its performance be good or poor if n is very large ....
Does there any other method ?
jesvh at 2007-11-9 13:53:03 >
# 11 Re: How to find the n-th record ?
Hi,
This is another method, I hope this will be good in performance compare with what you said.
set rowcount 1
SELECT TOP n field FROM table ORDER BY field ASC
Here no two TOP operations.
Regards
SivaR.
Sivar at 2007-11-9 13:54:02 >
# 12 Re: How to find the n-th record ?
Hi,
This is another method, I hope this will be good in performance compare with what you said.
set rowcount 1
SELECT TOP n field FROM table ORDER BY field ASC
Here no two TOP operations.
Regards
SivaR.
But it got the first (asc), last (desc) record .... not n-th
jesvh at 2007-11-9 13:55:00 >
# 13 Re: How to find the n-th record ?
Here a method, I am sure it will work ok:
declare @nr1 int
declare @nr2 int
set @nr1=2
set @nr2=5
--
select * from
(SELECT ROW_NUMBER () OVER ( ORDER BY col1) AS rowNum,col1, col2
FROM tbl ) as A
where A.rowNum between @nr1 and @nr2
order by A.col2
So, if your @nr1 and @nr2 (sent as parametyers to stored proc) have both the same value, returns 1 record, else more records, between @nr1 and @nr2
Hope it helped
# 14 Re: How to find the n-th record ?
How should I compose the SQL to get better performance?
Telling us what RDBMS youre using might be a help..
cjard at 2007-11-9 13:57:03 >
# 15 Re: How to find the n-th record ?
i'm also curious; why would you want the millionth row, or the 2000th row.. ? what kind of search uses this parameter? one day, the millionth row might return you Mr Smith. The next day it might return you Mrs Jones if many rows have been deleted. Why would you search in this way?
cjard at 2007-11-9 13:58:07 >
