Top Values, grouping

[datAdrenaline]

Just a quick note ... JET only looks at the last value for ties ... so for example ...

SELECT TOP 5 SomeField FROM SomeTable ORDER BY SomeField ASC

Can possibly yeild the following if the 6th row is > 200

SomeField
---------
100
100
100
100
200

But if the 6th row equals 200 .. you will get:

SomeField
---------
100
100
100
100
200
200

I just wanted to make sure that was noted as many don't realize the mechanism behind it.

 

[datAdrenaline]

Amazingly enough ... all our VBA code is in a BLOB ... so ... isn't nice to know that all that hard work is just one big blob of goo.

 

[dallr]

Leigh
I guess I have to or possibly "compact and repair" the old brain to get thing back in order. LOL

Brent
I believe the OP noticed that because of the information in the sample results they posted later down in this same thread. Nevertheless, I am glad you noted it because persons who are glancing through this post might not easily pick it up.

Later guys.

Dane

 

[ChadW]

Thanks for the code, it worked great. However, the same problem still remains addressed earlier.

Just a quick note ... JET only looks at the last value for ties ... so for example ...

SELECT TOP 5 SomeField FROM SomeTable ORDER BY SomeField ASC

Can possibly yeild the following if the 6th row is > 200

SomeField
---------
100
100
100
100
200

But if the 6th row equals 200 .. you will get:

SomeField
---------
100
100
100
100
200
200

I just wanted to make sure that was noted as many don't realize the mechanism behind it.

Is there no way around this.

My Scores table as you thought does have ScoreID (Autonumber) as its primary key.

 

[LPurvis]

Are you sure you've used the code as suggested?

 

[ChadW]

Yes, tried it for the top 3 results on a sample data base I have. If the 4th best result had the same value as the 3rd best this was calculated in the points total.

ie.

40,40,39,39,38, the total should be 119 (For top 3) but is returning 158.

 

[LPurvis]

Right you are - there was another problem with the original query that I'd just taken as was (as you'd, rightly said it was working apart from one issue - which seemed like it was only due to the With Ties consideration - but there's another reason too).

The use of Points as the critiera to link the corrolated subqueries isn't appropriate - as it just compounds the tied points issue.
We need to, again, use the primary key value to retrieve only the required rows.
i.e.

SELECT S.MemberID
     , S.Points
     , ST.TotalPoints
FROM Scores  S 
     INNER JOIN  (SELECT SUM(S.Points) As TotalPoints, S.MemberID 
                  FROM Scores S
                  WHERE S.ScoreID IN (SELECT TOP 12 SS.ScoreID 
                                     FROM Scores SS 
                                     WHERE SS.MemberID = S.MemberID
                                     ORDER BY SS.Points DESC, SS.ScoreID) 
                  GROUP BY MemberID) ST 
       ON  S.MemberID = ST.MemberID
WHERE (((S.ScoreID) In (SELECT TOP 12 SSub.ScoreID 
                       FROM Scores SSub 
                       WHERE SSub.MemberID = S.MemberID
                       ORDER BY SSub.Points DESC, SSub.ScoreID)))
ORDER BY ST.TotalPoints ,  S.MemberID, S.Points

Do bear in mind though what I said way back when about the potential for letting the report do much of this.
Calculating the related total for every row of the query is slight overhead overkill.

Cheers.

 

[ChadW]

Leigh you are a star, works perfect.

Thank you very much

Chad