Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59

    Question Unanswered: TOP ignored with ORDER BY in query

    In Access 2000:

    I have a query where I set "Top Values" to 200. It returns 200 records.

    Then I add a sort on one of the fields. It returns 213 records!

    It seems to completely ignore the TOP 200 when there is an ORDER BY in the query.

    Anyone run into this before? Can anyone reproduce this behavior? Am I missing something really fundamental here?

    Brian
    Last edited by bri; 11-21-02 at 16:24.

  2. #2
    Join Date
    Nov 2002
    Posts
    10

    Query SQL

    If you open the query and swith to SQL view, what is the text of the query?

  3. #3
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59

    Re: Query SQL

    Returns 200 records:

    SELECT TOP 200 Namelist_T.*
    FROM Namelist_T
    WHERE (((Namelist_T.Date1_DT) Between #8/11/2002# And #7/7/2002#));

    Returns 244 records

    SELECT TOP 200 Namelist_T.*
    FROM Namelist_T
    WHERE (((Namelist_T.Date1_DT) Between #8/11/2002# And #7/7/2002#))
    ORDER BY Namelist_T.Date1_DT;

    The ORDER BY is the only difference

  4. #4
    Join Date
    Nov 2002
    Posts
    10

    !&#*^@MS

    My only suggestion is explicitly referrencing each one of the fields you want to return rather than relying on Namelist_T.*

    ...Sorry I can't do better.

  5. #5
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59

    Talking SOLVED (sort of)

    This is wacky!

    If the sort field of the 'last' record has other records with the same value those records will be added to the result set until Access finds the next unique value in the sort field.

    For example:
    Code:
     
    tblPets
    #, guy, pet
    -------------
    1, joe, dog
    2, ken, cat
    3, bob, bunny
    4, sam, cat
    5, tim, dog
    6, dan, cat
    7, sid, gerbil
    8, don, fish
    9, ben, dog
    
    SELECT TOP 5 * FROM tblPets
    ORDER BY pet
    
    Returns:
    3, bob, bunny
    2, ken, cat
    6, dan, cat
    4, sam, cat
    1, joe, dog
    5, tim, dog
    9, ben, dog
    2 more records than we asked for because Access want's to make sure we didn't miss any dogs.

    However:
    Code:
     
    SELECT TOP 4 * FROM tblPets
    ORDER BY pet
    
    Returns:
    3, bob, bunny
    2, ken, cat
    6, dan, cat
    4, sam, cat
    4 records just like we asked.

    The solution:

    Add a unique field to the ORDER BY
    Code:
     
    SELECT TOP 5 * FROM tblPets
    ORDER BY pet, #
    
    Returns:
    3, bob, bunny
    2, ken, cat
    4, sam, cat
    6, dan, cat
    1, joe, dog
    Just wacky,

    Brian

Posting Permissions

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