Hi,
Either rank, either top. With a top:
SELECT a.*
FROM myTable As a
WHERE a.Score IN( SELECT TOP 2 b.score
FROM myTable As b
WHERE b.province = a.province
ORDER BY b.score DESC)
ORDER BY a.province, a.score DESC
The last order by is just for presentation and is not required by the logic.
The logic is, given a record, in a, keep all the record, from the same
table, that belongs to the same province, and order them by their score.
Keep the top 2 best score. Back to our initial record, if its score is one
of the scores just returned, keep it.
With a rank, we rank the records (first, second, third, ... ) accordingly to
their score, by (group) province:
SELECT a.province, a.score, LAST(NGOname), COUNT(*) as Rank
FROM myTable As a INNER JOIN myTable As b
ON a.province=b.province
AND a.score <= b.score
GROUP BY a.province, a.score
HAVING COUNT(*) <= 2
Both solutions are "inefficient", the first one compute the sub-select
query, the same, many times, uselessly, in theory, it could run it just once
per province; while the second one ranks all the records which is useless to
us to know who is 100th or 1000th, but it does it none the less. I think a
much more efficient formulation is possible with MS SQL Server 2005 using a
Table Expression to recursively union all the result of the SELECT TOP 2,
but being run just ONCE, by province.
Hoping it may help,
Vanderghast, Access MVP
