CODEWITHSUNDEEP
ms-accessms-access-2007vba
KSM
KSM

Reputation: 262

Max and Equality DataSet Query

Say i have a table like below, so my goal is to find the max points for the matching games and not ID, however if the points are equivalent such as 1-x-5 and 3-x-5 i want to ignore it from the data set. Can anyone help me thank you!

id game point
1    x   5
1    z   4
2    y   6
3    x   5
3    y   5
3    z   8

therefore only giving me 

id game point
2   y    6
3   z    8

ive tried some coding below but the data is not correct 

SELECT T1.ID, T1.Game, T1.Point
FROM TableName As T1
INNER JOIN (SELECT T2.ID, Min(T2.Point) As MinOfPoint
FROM TableName As T2
GROUP BY T2.ID) As SQ
ON T1.ID=SQ.ID And T1.Point=SQ.MinOfPoint

Upvotes: 2

Views: 52

Answers (2)

Karl Kruse
Karl Kruse

Reputation: 146

How about this? It's a little dirty but I think it does the trick.

SELECT T3.id, T3.game, T3.point
FROM TableName AS T3
INNER JOIN
    (SELECT T1.game, MAX(T1.Mpoint) AS RPoint
    FROM
        (SELECT game, MAX(point) AS MPoint
        FROM TableName
        GROUP BY game, point
        HAVING COUNT(point) = 1) AS T1
    GROUP BY T1.game) AS T2
ON T2.game = T3.game
   AND T2.RPoint = T3.point;

Upvotes: 1

KevenDenen
KevenDenen

Reputation: 1726

This should do what you're looking for, if I properly understood what you were trying to accomplish at least.

select T1.Game, T1.Point
from (select T1.Game, Max(T1.Point) as MaxPoint from T1 Group By T1.Game) as GroupData inner join T1 on GroupData.Game = T1.Game AND GroupData.MaxPoint = T1.Point 
group by T1.game, T1.point 
having Count(T1.id) = 1;

Upvotes: 0

Related Questions