What order is used by the First () function?

Question

Why do the following two queries return equivalent 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) 

This is confusing given that I manually determine the order of the subqueries.

Answer 1

The order of the results in the subquery does not matter if you do not use TOP in the subquery, which is not here. Most SQL variants do not allow this syntax — using ORDER BY in a subquery causes, for example, an error in SQL Server.

There is no ORDER BY in the top-level query, so the terms FIRST or TOP 1 are undefined in the context of this query.

In reference documents, Microsoft states (focus):

Since records are usually returned without any particular order (except for the query includes an ORDER BY clause), the records returned by these functions will be arbitrary .

Answer 2

I suspect that despite ORDER BY in the subquery, the subquery result set is assigned to the parent query as an unordered set of rows. In this case, the row order is not consistent / predictable / reliable. If you are interested, do an online search for Jet SHOWPLAN and look at the query plan that Access uses to fulfill your query. This was fairly easy to do with Jet in Access 2003. However, I did not do this with the new ACE engine.

There may be more, but I usually avoid the First() and Last() functions.

Answer 3

Your statements are ideal functional equivalents for
SELECT Min(Score) FROM Scores and
SELECT Max(Score) FROM Scores .
If you really want to get the first and last point, you'll need an AutoNumber or DateTime field to indicate the order in which you enter. Then you can request:

 SELECT First(Score), Last(Score) FROM Scores ORDER BY MySortKey 

If you save your question, the correct syntax would be SELECT FIRST(score) FROM (SELECT score FROM scores) ORDER BY score ASC ,
or simplified
SELECT FIRST(score) FROM scores ORDER BY score ASC