How to choose a row based on the maximum quantity? (using standard sql)

Question

I have a table:

ComputerID, UserID, LoginDate

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

For computers, all , but for each computer, only one user is displayed , which is most often registered on this computer . (If there is a tie, I think I would just like to randomly select one of the users ... so this would seem to indicate that I need TOP-1 somewhere.)

(This is in ms-access, therefore, cannot use provider-specific functions).

Solution (small correction 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

, a > 1 loginCount , , , , Max (UserID)..... . , , , :

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

, :

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

MS Access two . , . .

:

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);

. , "" , . UNIQUE FIRST . , computerID.

2. , MySQL, , Access.

3. , Access. , .

Answer 3

SELECT TOP 1 * FROM YOURTABLE ORDER BY LoginCount DESC

(MS-SQL, , Access)

:

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

Answer 4

, MS ACCESS, SQL Server 05 08, .

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

Answer 5

Access:

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 6

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;