It is better / more efficient to use subqueries or SELECT statements in the WHERE clause (in MS Access)

Question

I have an MS Access database containing a table of dates, ratings, and the people these ratings are associated with, for example

  Date Score Name
 1/6/11 5 Dave
 1/6/11 10 Sarah
 2/6/11 4 Dave
 2/6/11 3 Graham
 3/6/11 1 Dan
 4/6/11 11 Dan
 4/6/11 9 Graham

I would like to write a query to find who got the highest socre in every day, i.e. result:

  Date Score Name
 1/6/11 10 Sarah
 2/6/11 4 Dave
 3/6/11 1 Dan
 4/6/11 11 Dan

I can think of two solutions to this problem (I am open to alternative suggestions):

1) Write a query to find the minimum score for each date, and then a second query connecting the first query with the original table. i.e:.

Query1:

SELECT Date, MAX(Score) AS MaxScore FROM ScoresTable GROUP BY Date 

Query2:

 SELECT ScoresTable.* FROM ScoresTable INNER JOIN Query1 ON ScoresTable.Date = Query1.Date AND ScoresTable.Score = Query1.MaxScore 

[They can be combined as one request:

 SELECT ScoresTable.* FROM ScoresTable INNER JOIN (SELECT Date, MAX(Score) AS MaxScore FROM ScoresTable GROUP BY Date) Query1 ON ScoresTable.Date = Query1.Date AND ScoresTable.Score = Query1.MaxScore 

but I prefer to keep them separate to make it easier for other users, i.e. they can use the Access interface without knowing SQL]

2) Write one query with another simple query in the WHERE clause (this is the new method I just read about, does it have a name?), I.e.

 SELECT * FROM ScoresTable WHERE Score = (SELECT MAX(Score) FROM ScoresTable AS st WHERE st.Date = ScoresTable.Date) 

The latter is clearly more elegant, but seems to be slower. Which option is better? Datasets can be quite large.

Answer 1

Your only composite query already looks optimal for me, I doubt you can make it simpler or more efficient.
The wise use of indexes in your table should ensure that the query is pretty fast.

Your last query is called Correlated Subquery .
This is sometimes useful, but can be very slow: a subquery must be performed for each record in ScoresTable, because the result of the subquery depends on the value of each individual record in ScoresTable. It is very difficult to optimize the database engine.

If you are interested in learning more about how the query optimizer optimizes your queries, look at these articles, they will show you what's under the hood:

• Use Microsoft Jet ShowPlan to Write More Effective Queries
• Desktop 2002 Developers Guide, Chapter 15: Optimizing Applications