CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2002
    Posts
    56

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

  2. #2
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: How to find the n-th record ?

    PHP Code:
    SELECT TOP 1 FROM (SELECT TOP 2 FROM TABLENAME ORDER BY COLUMNNAME DESCA 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.

  3. #3
    Join Date
    Mar 2006
    Location
    Graz, Austria
    Posts
    273

    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

  4. #4
    Join Date
    Mar 2002
    Posts
    56

    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.

  5. #5
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    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 

  6. #6
    Join Date
    Jun 2006
    Posts
    437

    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.

  7. #7
    Join Date
    Mar 2006
    Location
    Graz, Austria
    Posts
    273

    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

  8. #8
    Join Date
    Jun 2006
    Posts
    437

    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.

  9. #9
    Join Date
    Mar 2002
    Posts
    56

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

  10. #10
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: How to find the n-th record ?

    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?

  11. #11
    Join Date
    Mar 2002
    Posts
    56

    Re: How to find the n-th record ?

    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 ?

  12. #12
    Join Date
    Jun 2006
    Location
    Chennai,India
    Posts
    21

    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.

  13. #13
    Join Date
    Mar 2002
    Posts
    56

    Re: How to find the n-th record ?

    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

  14. #14
    Join Date
    Mar 2006
    Location
    Graz, Austria
    Posts
    273

    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

  15. #15
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    Re: How to find the n-th record ?

    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..
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured