Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2002
    Posts
    1

    Unanswered: Select values from row 5 to end of table. HOW?

    As the subject says... How do I select all rows between row 5 and the end of the table...

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    There was recently another thread about how to select records in reverse order. That solution + a select top <number of records - 5>
    could do it... Not funny but (I guess) it works - haven't tried.

  3. #3
    Join Date
    Sep 2002
    Posts
    7

    Re: Select values from row 5 to end of table. HOW?

    To finish this task, using CURSOR is the best way.

  4. #4
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    No its' not.

    Set theory is preferrable over Cursors in almost all cases.

    A
    There have been many posts made throughout the world.
    This was one of them.

  5. #5
    Join Date
    Sep 2002
    Posts
    7
    select *
    from <table>
    where <Primary_key_Columns> not in
    (select top 5 <Primary_key_Columns> from <table>)
    Last edited by brownjiang; 11-01-02 at 02:12.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    >> where <Primary_key_Columns> not in
    >> (select top 5 <Primary_key_Columns> from <table> )

    brownjiang, i'm not sure if that will work when there is more than one pk column

    also the subquery would need an ORDER BY

    bunce, i totally agree with you, but disagree that this is one of those cases

    set theory does not acknowledge "top" operators, so you would have to use the generic set-based solution, which is

    Code:
    select pk1
         , pk2
         , field1
         , fieldn 
      from thetable X 
     where ( select count(*) 
               from thetable 
              where pk1 > X.pk1
                and pk2 > X.pk2 ) > 5
     order 
         by pk1 desc
          , pk2 desc
    and that's not nearly as efficient as a simple table sort followed by something (whether "top" or cursor logic) to choose the right rows

    rudy
    http://rudy.ca/

  7. #7
    Join Date
    Nov 2002
    Location
    Orlando
    Posts
    1

    Wink

    Are we forgetting that there is no significance to the order in which records are stored in a table or returned in an unordered query?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm not forgetting it, i did suggest the ORDER BY was missing

  9. #9
    Join Date
    Sep 2002
    Posts
    7
    ok~

    pk1 and pk2 ... must be converted to char type.

    The new query:

    Code:
    select *
      from <table>
    where (pk1+pk2+...)
     not in (select top 5 (pk1+pk2+...) from <table> order by <order_field>)
     order by <order_field>

Posting Permissions

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