CODEWITHSUNDEEP
sqlms-access
drewmagoo
drewmagoo

Reputation: 37

ms access max count

Here is my query:

SELECT Count(*) AS CountOfJoker, tblPrediction.GameID
FROM (tblUser INNER JOIN (tblPool INNER JOIN tblUserPool ON tblPool.PoolID = tblUserPool.PoolID) ON tblUser.UserID = tblUserPool.UserID) INNER JOIN (tblResult INNER JOIN tblPrediction ON tblResult.GameID = tblPrediction.GameID) ON tblUser.UserID = tblPrediction.UserID
GROUP BY tblPrediction.GameID, tblPrediction.Joker, tblResult.GroupName, tblPool.PoolID
HAVING (((tblPrediction.Joker)=True) AND ((tblResult.GroupName)='A') AND ((tblPool.PoolID)=314));

Here is the output:

CountOfJoker    GameID
2       1
2       2
2       9
1       10
1       17
3       18

I would like the MAX of CountOfJoker with the following output:

CountOfJoker    GameID
3       18

Thanks Drew

Upvotes: 0

Views: 863

Answers (1)

StrubT
StrubT

Reputation: 1028

You could use select top and order by ... desc

    SELECT TOP 1 Count(*) AS CountOfJoker, tblPrediction.GameID
    FROM (tblUser INNER JOIN (tblPool INNER JOIN tblUserPool ON tblPool.PoolID = tblUserPool.PoolID) ON tblUser.UserID = tblUserPool.UserID) INNER JOIN (tblResult INNER JOIN tblPrediction ON tblResult.GameID = tblPrediction.GameID) ON tblUser.UserID = tblPrediction.UserID
    GROUP BY tblPrediction.GameID, tblPrediction.Joker, tblResult.GroupName, tblPool.PoolID
    HAVING (((tblPrediction.Joker)=True) AND ((tblResult.GroupName)='A') AND ((tblPool.PoolID)=314));
    ORDER BY Count(*) DESC

Upvotes: 1

Related Questions