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

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.

Answer 2

SELECT TOP 1 * FROM YOURTABLE ORDER BY LoginCount DESC

(MS - SQL, not sure this works in Access)

Try some variation on this example:

SELECT UserId, MAX(COUNT(*)) as "HighestLogin"
FROM YOURTABLE
GROUP BY ComputerId;

Answer 3

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 4

Could you check this on your Access database:

select ComputerID, UserID, count(*) as LoginCount
from t2  parent_table
group by ComputerID, UserID
having count(*) = (select max(count(*) ) from t2 
       where ComputerID = parent_table.ComputerID group by ComputerID, UserID)
order by ComputerID, UserID

Answer 5

create temporary table t select ComputerID, UserID, count(*) as LoginCount
from table group by ComputerID, UserID;

create temporary table m select ComputerID, max(LoginCount) as maxLoginCount
from t group by ComputerID;

select m.ComputerID, max(UserID) from m join t
on m.ComputerID = t.ComputerID and m.maxLoginCount = t.LoginCount
group by ComputerID;

Answer 6

not sure if this is available in MS ACCESS, it is for sure available in SQL Server 05 & 08 so you will have to investigate that.

you can use the handy ROW_NUMBER() function that will allow you to partition and assign a row number to a subset of the data and then you can limit the results brought back based upon that row number.

DECLARE     @MaxResultsPerComputerID    INT
SELECT      @MaxResultsPerComputerID    = 3 -- assign the maximum number of results to bring back per computer

SELECT      *
FROM        (
            SELECT      ComputerID, 
                        UserID, 
                        LoginDate,
                        NumberOfTimesLoggedIn, -- Derive this value somehow (not sure what your db schema is)
                        ROW_NUMBER() OVER (PARTITION BY ComputerID ORDER BY NumberOfTimesLoggedIn) AS RowNumber -- the magic happens here
            FROM        SomeTable
            ) a
WHERE       RowNumber <= @MaxResultsPerComputerID