How to find the n-th record ?

[jesvh]

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

[Shuja Ali]

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.

[DanielaTm]

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

[jesvh]

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.

[Shuja Ali]

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 

[davide++]

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.

[DanielaTm]

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

[davide++]

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.

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

[Shuja Ali]

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?

[jesvh]

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 ?

[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.

[jesvh]

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

[DanielaTm]

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

[cjard]

How should I compose the SQL to get better performance?

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