SELECT TOP 3 returns more records

[winshent]

Hi Guys

I'm a bit confused by this..

My statement below is current returning 4 records. Two of the records have the same GBPAmount value.

RequestID is the Primary Key

SELECT TOP 3 RequestID, GBPAmount, Currency, RequestDate
FROM PayRequest
WHERE (((Currency)="CAD Canadian Dollar") AND ((RequestDate)>#11/16/2014#))
ORDER BY GBPAmount;

RequestID  GBPAmount   Currency               RequestDate
10207         8.17     CAD Canadian Dollar    03/02/2015
9874         33.82     CAD Canadian Dollar    20/01/2015
11327       109.58     CAD Canadian Dollar    23/02/2015
10495       109.58     CAD Canadian Dollar    05/02/2015

Can anyone explain this ?

 

[winshent]

Its just occurred to me that the problem is caused by the ORDER statement..

If i RequestID to the ORDER statement it works fine..

The SQL now looks like this..

SELECT TOP 3 RequestID, GBPAmount, Currency, RequestDate
FROM PayRequest
WHERE Currency="CAD Canadian Dollar" AND RequestDate>#11/16/2014#
ORDER BY GBPAmount, RequestID;

School boy error I guess

 

[plog]

Just a misunderstanding of how TOP and ORDER work together:

https://support.office.com/en-sg/ar...edicates-24f2a47d-a803-4c7c-8e81-756fe298ce57

You initially ordered by just GBPAmount and there was a tie for 3rd place. Access doesn't get involved in ties by itself so it returned 4 results (top 3 values of ORDER BY field). Your new version had a more specific ORDER BY clause so it ordered them by GBPAmount then RequestID. When it did that it had a clear 3rd place.

 

[winshent]

Just a misunderstanding of how TOP and ORDER work together:

https://support.office.com/en-sg/ar...edicates-24f2a47d-a803-4c7c-8e81-756fe298ce57

You initially ordered by just GBPAmount and there was a tie for 3rd place. Access doesn't get involved in ties by itself so it returned 4 results (top 3 values of ORDER BY field). Your new version had a more specific ORDER BY clause so it ordered them by GBPAmount then RequestID. When it did that it had a clear 3rd place.

Yes, this only just clicked after I posted it.. As the DB engine cannot guarantee returning the same 3 records each time the SQL is run, so needs further criteria..