-
July 3rd, 2006, 09:12 PM
#1
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 !!
-
July 4th, 2006, 12:53 AM
#2
Re: How to find the n-th record ?
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.
-
July 4th, 2006, 01:35 AM
#3
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
Daniela
******
I would love to change the world, but they won't give me the source code
-
July 4th, 2006, 10:20 PM
#4
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.
-
July 5th, 2006, 12:00 AM
#5
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
PHP Code:
SELECT TOP 1 * FROM table
WHERE .........
ORDER BY field DESC
-
July 5th, 2006, 03:57 AM
#6
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.
-
July 5th, 2006, 04:17 AM
#7
Re: How to find the n-th record ?
Yes, it was a confusion about you requirements.
I agreed with Shuja Ali
Daniela
******
I would love to change the world, but they won't give me the source code
-
July 5th, 2006, 07:43 AM
#8
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.
-
July 5th, 2006, 08:58 PM
#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 !!
-
July 6th, 2006, 02:17 AM
#10
Re: How to find the n-th record ?
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?
-
July 7th, 2006, 02:22 AM
#11
Re: How to find the n-th record ?
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 ?
-
July 7th, 2006, 08:41 AM
#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.
-
July 9th, 2006, 08:38 PM
#13
Re: How to find the n-th record ?
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
-
July 12th, 2006, 04:40 AM
#14
Re: How to find the n-th record ?
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
Daniela
******
I would love to change the world, but they won't give me the source code
-
July 13th, 2006, 05:46 AM
#15
Re: How to find the n-th record ?
Originally Posted by jesvh
How should I compose the SQL to get better performance?
Telling us what RDBMS youre using might be a help..
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|