How to select a row based on a max count? (using standard sql)

Question

I have a table:

ComputerID, UserID, LoginDate

I need a select statement that returns:
ComputerID, UserID, LoginCount

For all computers, but for each computer, showing just the one user that logged on most often to that computer. (If there is a tie, I guess I would want to just arbitrarily pick one of the users....so this would seem to indicate I need a TOP 1 somewhere.)

(This is in ms-access, so can't use vendor specific functionality).

Solution (slight fix of JBrooks answer)

select main.*
from (select ComputerID, UserID, count(1) as cnt
    from ComputerLoginHistory
    group by ComputerID, UserID) as main
           inner join (select ComputerID, max(cnt) As  maxCnt
                            from 
                                    (select ComputerID, UserID, count(1) as cnt
                                     from ComputerLoginHistory
                                     group by ComputerID, UserID) as Counts
                            group by ComputerID)
                          as maxes
on main.ComputerID = maxes.ComputerID
and main.cnt = maxes.maxCnt

To handle the situation where >1 user may have the same loginCount for a given computer, about all I can think of to wrap another select around this, selecting the Max(UserID).....so you are basically just arbitrarily picking one of them. That's what I've done in this example, where I am pulling back the most recent user, rather than the most active user:

Select ComputerID, Max(xUserID) As UserID, MaxLoginDate
FROM
(
SELECT main.ComputerID, main.UserID as xUserID, main.MaxLoginDate
FROM [select ComputerID, UserID, Max(LoginDate) as MaxLoginDate
    from ComputerLoginHistory
    group by ComputerID, UserID]. AS main 
     INNER JOIN [select ComputerID, Max(MaxLoginDate) As MaxLogin
                            from 
                                    (select ComputerID, UserID, Max(LoginDate) as MaxLoginDate
                                     from ComputerLoginHistory
                                     group by ComputerID, UserID) as Counts
                            group by ComputerID]. AS maxes ON (main.MaxLoginDate = maxes.MaxLogin) AND (main.ComputerID = maxes.ComputerID)
)
GROUP BY ComputerID, MaxLoginDate
ORDER BY ComputerID

Answer 1

If you want a listing of each computer Id with its top user it would be something like:

select main.*
from (select ComputerID, UserID, count(1) as cnt
    from logTable
    group by ComputerID, UserID) as main
    inner join (select ComputerID, max(cnt) maxCnt
                from (select ComputerID, UserID, count(1) as cnt
                    from logTable
                    group by ComputerID, UserID) as Counts) 
                as maxes
on main.ComputerID = maxes.ComputerID
and main.cnt = maxes.maxCnt

Answer 2

In MS Access, I would write two three queries and call the second query from the first query. The first query should simply do the count, and the next query will find the max of the first query. The third query will reference the user ID in the first query after joining both of the first 2 queries together.

For example:

qryCountQuery:

SELECT 
LoginCount.ComputerID, 
LoginCount.UserID, 
Count(LoginCount.ComputerID) AS CountOfComputerID
FROM 
LoginCount
GROUP BY 
LoginCount.ComputerID, LoginCount.UserID;

qryMaxQuery:

SELECT 
qryCountQuery.ComputerID, 
Max(qryCountQuery.CountOfComputerID) AS MaxOfCountOfComputerID
FROM 
qryCountQuery
GROUP BY 
qryCountQuery.ComputerID;

qryCountMaxQueryCombined:

SELECT 
qryMaxQuery.ComputerID, 
qryMaxQuery.MaxOfCountOfComputerID, 
qryCountQuery.UserID
FROM 
qryCountQuery 
INNER JOIN qryMaxQuery 
    ON (qryCountQuery.CountOfComputerID = qryMaxQuery.MaxOfCountOfComputerID) AND 
       (qryCountQuery.ComputerID = qryMaxQuery.ComputerID);

NOTE: If you have users that are "tied" for the most logins to one PC, the PC will show up twice with both users. You could throw in a UNIQUE or throw a FIRST aggregate call into yet another query. It depends on if you absolutely must have only one result per computerID.

NOTE2: In another system such as MySQL I might use nested queries, but I prefer to separate it out in Access.

NOTE3: I forgot this problem was difficult in Access. I'm glad I tested my code.