Bug with SELECT TOP when ordering

[Guest]

Hi,

I need to get only the first few results from a query in Access.
Unfortunately there appears to be a serious bug with Access' implementation
of SELECT TOP.

To duplicate this bug:

Create a table called TestTable
Add field: CustName (Text)
Add field: City (Text)

Add some test data making sure to add several entries with the same city
name. Then run:

SELECT TOP 3 TestTable.CustName, TestTable.City
FROM TestTable
ORDER BY TestTable.City;

With my test data I end up with 6 records in the results of this query. It
appears that when you combine SELECT TOP with ORDER BY, if the contents of
the field you are ordering on contain duplicates then Access may return all
of the duplicate rows as well.

Is there any way around this?

Thanks
Ben

 

[John Spencer]

That is not a bug. It works as advertised and returns the ties for the last
position. Other data engines work differently and can return results with
or without ties.

If you want to get unique values make sure your Order By clause will sort
uniquely. Simplest way to do that is to include the primary key (or keys if
multiple tables are involved) as the last item in the sort. That will
return exactly the number of items you specify.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..