Access SQL using TOP 5 returning more than 5 results?

Question

I'm using the following statement

SELECT TOP 5 rootcause, COUNT(IIF(accountability="Team 1",1,0))
FROM  MOAQ
WHERE CDT=1
GROUP BY rootcause

MOAQ is another query that returns about 20 fields from 4 tables, nothing special. This works as expected and I get 5 results.

If I add an ORDER BY clause on the conditional field though I start to get 8 results. If I sort by the first field there is no problem.

Anyone know what might be going on?

Edit to clarify - I am only testing from within Access 2003 at this point, eventual statement will be parameterized query via ADO from Excel front end.

Answer 1

This a known effect of the top directive in Access, but it's not very well known...

The top directive doesn't return the top n items, as one is easily led to believe. Instead it returns at least n distinct items determined by the ordering of the result.

In most cases it's the same, but in your example where the 5th to 8th items have the same ordering value, all of them are included. It returns the first five items, but then also all items that have the same ordering value as the fifth item.

If you don't apply any ordering to the table, all fields are considered, so if you have a unique field in the result the query will always return five items. The same of course if the unique field is included in the ordering.

Other dialects of SQL may behave differently. The top directive alone in T-SQL (SQL Server) for example never returns more than n items. However, by specifying the clauses with ties and order by along with top, one can observe the same behavior as in Access.

Answer 2

Go to the page in the Access 2003 Help names About ANSI SQL query mode (MDB) then expand the 'Why use ANSI-92 SQL?' subject and you'll see this:

"Using the LIMIT TO nn ROWS clause to limit the number of rows returned by a query"

So just put the Access UI into ANSI-92 Query Mode or use OLE DB (e.g. ADO) in code and add the clause

LIMIT TO 5 ROWS

to your query.

...

Only kidding! This is merely another example of how the Access database engine is poorly documented. I think someone in the Access documentation team made the not-too-unreasonable assumption that the Access database engine's so-called ANSI-92 Query Mode would be compliant with the ISO/ANSI SQL-92 Standard. It is not.

The Access database engine has its own proprietary (i.e. non-SQL-92 Standard) syntax TOP n (not to be confused with SQL Servers own proprietary TOP n syntax which is semantically different). In a nutshell, it doesn't resolve duplicates. If the criteria satisfied every row in the table then you'd get every row in the table in the resultset.

The workaround is to use a cursor to read only the first n rows in the resultset. Because Access database SQL does not support procedural code or explicit cursors, you'll need to do this on the 'client' side e.g. open a Recordset object and read off the first n records into a fabricated ADO recordset.