The Underscore Archives

It may be due to the engine in MS Access, quite likely. You can probably
get around it by using a subselect and do the top 10 part as a query on
whatever the subselect returns.

Maybe a group by would help, it's all down to the query optimizer, but
with Access it probably scans through everything without indexes anyway :)

	Steve

On Tue, Dec 12, 2006 at 09:31:01PM +0000, Rick Hurst wrote:
> i'm getting bizzare results from a simple SQL query in MS Access:-
> 
> if I do something like:-
> 
> SELECT TOP 10 foo_id, foo_name, foo_modified from tbl_foo
> 
> I get back ten rows as expected,
> 
> however, if I attempt to order the results by foo_modifed (date/time field)
> I get back *all* the records, not just 10.
> 
> i.e.
> 
> SELECT TOP 10 foo_id, foo_name, foo_modified FROM tbl_foo ORDER BY
> foo_modified DESC
> 
> returns sometimes *thousands* of records
> 
> can anyone explain this? FWIW some of the foo_modified fields are null and
> i've tried filtering e.g "where foo_modified is not null"
> 
> 
> -- 
> Rick Hurst, Web developer, Bristol, England
> http://www.rickhurst.co.uk | http://www.netsight.co.uk
> -- 
> underscore_ list info/archive -> http://www.under-score.org.uk