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 !!

PHP Code:

---

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.

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

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.

If you are just going to look at the first record then why not do just this

PHP Code:

---

SELECT TOP 1 * FROM table
WHERE .........
ORDER BY field DESC 


---

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.

Yes, it was a confusion about you requirements.
I agreed with Shuja Ali

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.

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 !!

Quote:

---

Originally Posted by jesvh

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?

Quote:

---

Originally Posted by Shuja Ali

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 ?

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.

Quote:

---

Originally Posted by Sivar

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

Here a method, I am sure it will work ok:

Code:

---


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

Quote:

---

Originally Posted by jesvh

How should I compose the SQL to get better performance?

---

Telling us what RDBMS youre using might be a help..

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?