Search code examples
sqlms-accessms-access-2007

What order is used by First() function?

Why do the following two queries return identical results?

SELECT FIRST(score) FROM (SELECT score FROM scores ORDER BY score ASC)
SELECT FIRST(score) FROM (SELECT score FROM scores ORDER BY score DESC)

It's confusing, considering that I manually specify the order of subqueries.

Solution

  • The order of the results in the subquery is irrelevant, unless you use TOP within the subquery, which you don't here. Most SQL variants won't allow this syntax -- using an ORDER BY in a subquery throws an error in SQL Server, for example.

    Your top-level query has no ORDER BY, thus the concepts of FIRST or TOP 1 are undefined in the context of that query.

    In the reference docs, Microsoft states (emphasis mine):

    Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary.