A question about the top function in access

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.