I always thought that the access SQL statement was similar to that of the SQL server. At that time, the Select Top N was used for access tests. It was successful, but yesterday I suddenly found a problem with access:
If order by is used after the top query statement, and the order by field contains duplicate values, the top may be invalid,
All records are returned.
For example, select top 5 from news order by createdate
If there are duplicate values in createdate, it is very likely that all records will be displayed, and this top function will be invalid.
However, if no duplicate value exists in createdate, the top function is still valid.
Therefore, when using the top function in access, pay attention to whether the order by field has duplicate values. For example, if createdate is "year-month-day hour: minute: Second, that's basically not a problem.
But for the sake of insurance, we can also use the "add primary key" method:
Select top 5 from news order by createdate DESC, Id DESC
This problem can be prevented by using the primary key as a "non-repeating" safeguard !!
Hope this is useful to some friends !!
Find an explanation on the Internet:
Jet SQL is not a T-SQL statement.
Jet SQL returns a duplicate value, that is, if the order by field in a table is 0, there are a total of 100 records, even if you use select top 1 to return records, similarly, 100 records are returned, because Jet DB cannot judge the order in these 100 records, and only 100 records can be returned. To solve this problem, you can add a primary key field