SELECT TOP Function General Question

[Guest]

I have a database that I want to select the top teams that have wins.
However, If I use Select TOP 2 and there are three teams that have the same
amount of wins will the function return all 3 teams.

Or would I be better of doing it by using the Count Function and basing
another querry of that.

Thanks

 

[Guest]

The TOP option won't work reliably. To take account of ties for the top
places between 3 or more teams you can do it all in one query by using a
subquery:

SELECT *
FROM Teams AS T1
WHERE
(SELECT COUNT(*)
FROM Teams As T2
WHERE T2.Wins > T1.Wins) < 2;

This works by returning a row only if there are less than two rows in the
table with a greater number of wins. If 3 or more teams tie for the top
place then there will be zero rows with a lower number of wins for each of
these teams so they'll all be returned. For teams with lower numbers the
count will be at least 3 so none of them will be returned.

Ken Sheridan
Stafford, England

If all teams have different number of wins or only two have the highest
number of wins this will return two rows. If more than two have the same
number of wins and these are the highest then all of those teams will be
returned.

 

[Guest]

Thanks, I believe I seen this solution before back when in Access 2.0....It
was when you could call Microsoft Access support and you got 2 support
questions free. I think I still have that fax from Microsoft
somewhere...LOL...After that it would be $90.00 per question. After
reviewing all my posts I would be out a couple of grand.