SELECT TOP producing unpredictable results in Access

[SortedSites]

I’ve been using SELECT TOP in Access and getting some really unpredictable results, for example the following query returns 12 results. WHY?
I try varying the number of records selected for example TOP 1 returns two results. What the hell? Can anyone explain this?

SELECT TOP 5 
   A.[headline], 
   A.[description], 
   A.[articleid],  
   A.[date], 
   U.[userid], 
   U.[fullname], 
   U.[photo] 
FROM articles as A
INNER 
JOIN users as U 
on A.userid= U.userid 
WHERE A.published = true 
ORDER By A.[date] DESC

[SortedSites]

@SortedSites

I’ve got a fix, but I’m not understanding why this is necessary, does Access bug out ‘SELECT TOP’ statements WHERE joins are used?

SELECT TOP 5 * FROM (SELECT
   A.[headline], 
   A.[description], 
   A.[articleid],  
   A.[date], 
   U.[userid], 
   U.[fullname], 
   U.[photo] 
FROM articles as A
INNER 
JOIN users as U 
on A.userid= U.userid 
WHERE A.published = true 
ORDER By A.[date] DESC) as top5

[r937]

the answer is simple, msaccess implements TOP as though it were TOP WITH TIES (an option in mssqlserver)

thus your top 5 might be

2011-12-16 asdf
2011-12-15 qwer
2011-12-14 aaaa
2011-12-14 bbbb
2011-12-14 cccc
2011-12-14 dddd
2011-12-14 eeee
2011-12-14 oooo

apparently you want to throw away dddd, eeee, and oooo, despite the fact that they have the same criterion (for the TOP action) as aaaa, bbbb, and cccc

but if you have a “fix” then you’re okay, right?

[SortedSites]

“TOP WITH TIES?” Had never heard of that before.

I think I’m OK… I mean check my logic in my fix listed above. I’m just using a subquery to find ALL records matching my query then SELECTing TOP 5 FROM the subquery results. But is there a better way to make Access implement TOP (WITHOUT TIES).

Also for the benefit of anyone who Googles a similar problem, this is a non-issue if you are not using ORDER BY your SQL statement or if the value you are ordering by will be unique for each of the records returned by your Query.

[r937]

i personally have an issue with TOP cutting off rows that are tied for last place

as i indicated before, the ones you are cutting off have the same qualifications as the ones you are saving, and the arbitrary exclusion of qualified rows just doesn’t seem right

probably the easiest way to do what you want is to skip the sql gymnastics, just return TOP, and if this happens to include ties, then there shouldn’t be that many of them, and you can cut them off in your application logic a lot more easily

[SortedSites]

@r937

i personally have an issue with TOP cutting off rows that are tied for last place

as i indicated before, the ones you are cutting off have the same qualifications as the ones you are saving, and the arbitrary exclusion of qualified rows just doesn’t seem right

probably the easiest way to do what you want is to skip the sql gymnastics, just return TOP, and if this happens to include ties, then there shouldn’t be that many of them, and you can cut them off in your application logic a lot more easily

I had been using application logic before, but it makes for neater code if you can do it with SQL.

Ah… the Art and Zen of programming.