if you use Access 2000

[Guest]

I don't know if Microsoft knows this query has unexpected results.

SELECT roster.team, Avg(Val([Price])) AS FAprice
FROM roster
WHERE (((roster.player) In (select top 8 [PT].[player]
from [roster] as [PT]
where [roster].[team] = [PT].[team]
order by [PT].[Price] desc)))
GROUP BY roster.team;


( Avg(convert(float,[Price])) in SQL Server )

Why does the SQL Server result to this query not match the Access 2000
result? Data is the same. Problem is that, in spite of documentation to the
contrary, Access executes "top 8" as if were "top 8 with ties".

 

[Duane Hookom]

You can add whatever tie breaker you want (PlayerID) to the Order By
following the Price.
This is a known difference.