Puzzler - top 3 results

[Stewart Walker]

I've got a table with id and results. How would I find the
sum of the top 3 results for each ID - example table

ID Result
id1 5
id1 8
id1 2
id1 4
id1 1
id2 2
id2 2
id2 3
id2 4

I'd like the sum of id1 one to show 17 and id2 sum to be 9
(so it would discount the other 2 as I only want the top 3
results added together)

I hope this makes sense and that someone can work out how
to do this!!!!

TIA

Stewart
MOS - Excel Expert (maybe I should of learned more about
Access!)

 

[Michel Walsh]

Hi,


SELECT ID, SUM(Result) - iif( COUNT(*) <= 3, 0, COUNT(*)-3) * MIN(Result)
FROM

(
SELECT a.ID, a.Result
FROM myTable As a LEFT JOIN myTable As b
ON a.ID=b.ID AND a.Result< b.Result
GROUP BY a.ID, a.Result
HAVING COUNT(*)<= 3
)

GROUP BY ID



The virtual table just try to pick a TOP 3. As usual, it keep ex-equo, so,
with you data, it should return:


id1 8
id1 5
id1 4
id2 4
id2 3
id2 2
id2 2


( four values for id2).

The top most query will recognize you have more than 3 values, in this
group, and will compute:

SUM - (COUNT(*)-1) * 2
ie:
(4+3+2+2) - (1) * 2

or 9.



Hoping it may help,
Vanderghast, Access MVP