CODEWITHSUNDEEP
sqlms-accessms-access-2007
Nelson Tatius
Nelson Tatius

Reputation: 8043

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.

Upvotes: 6

Views: 2841

Answers (3)

DigitalDan
DigitalDan

Reputation: 2637

To answer the question directly:

Access ignores the ORDER BY clause in most subqueries. I beleive (but can't prove) this is due to bugs/limitations in the query optimiser, although it's not documented anywhere (that I could find). I've tested lots of SQL using Access 2007 and Access 2016 to come to this conclusion.

To make the examples work as expected:

Add TOP 100 PERCENT to the subqueries:

SELECT FIRST(score) FROM (SELECT TOP 100 PERCENT score FROM scores ORDER BY score ASC)
SELECT FIRST(score) FROM (SELECT TOP 100 PERCENT score FROM scores ORDER BY score DESC)

When to use First/Last instead of Max/Min:

A good example of when you'd want to use this approach instead of the simpler Min and Max aggregate functions is when there's another field that you want from the same record, e.g if the underlying scores table also held the names of players and the rounds of the game, you can get the name and score of the best and worst player in each round like this:

SELECT
  round, FIRST(name) AS best, FIRST(score) AS highscore, LAST(name) AS worst, LAST(score) AS lowscore
FROM
  (SELECT TOP 100 PERCENT * FROM scores ORDER BY score DESC)
GROUP BY
  round

Upvotes: 5

iDevlop
iDevlop

Reputation: 25262

Your statements are a perfect functional equivalents to
SELECT Min(Score) FROM Scores and
SELECT Max(Score) FROM Scores.
If you really want to retrieve the first and last score, you will need an AutoNumber or a DateTime field to indicate the input order. You could then query: 

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

If you persist with 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

Upvotes: -2

ExactaBox
ExactaBox

Reputation: 3395

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.

Upvotes: 3

Related Questions