how to write this sql statement?

Question

id  login_name login_time
1    aa        2002-09-19
2    bb        2002-12-19
3    bb        2002-12-30

How do I write one SQL statement to find out the latest login_time for the user who has the maximum number of logins. For example, in this sample data, user "bb" has logged in twice and his latest login_time is '2002-12-30'.

Answer 1

select * 
from [login table] 
where login_name [has max] on login_time [is soonest]

Answer 2

SELECT login_name, COUNT(*) as num_logins, max(login_time) as last_login_time
FROM table_name ORDER BY num_logins DESC 

Then, depends on sql server you are using, you can add limit 1 (in case of mysql), or top 1 for MS SQL Server, or something else for a different server to get the first record only.

Answer 3

The following works on an Oracle database:

SELECT MAX(LOGIN_TIME)
  FROM LOGIN_TABLE
  WHERE LOGIN_NAME = (SELECT LOGIN_NAME
                        FROM (SELECT LOGIN_NAME, COUNT(*) AS LOGIN_COUNT
                                FROM LOGIN_TABLE
                                GROUP BY LOGIN_NAME
                                ORDER BY LOGIN_COUNT DESC)
                        WHERE ROWNUM = 1)

Share and enjoy.

Answer 4

Here's the easy way:

SELECT
    t.login_name
    ,COUNT(t.id) AS login_counts
    ,MAX(t.login_time) AS latest_login_time
FROM this_table AS t
GROUP BY t.login_name
ORDER BY login_counts DESC, login_name
;

The top line gives you the login_name with the most logins.

And here's the hard way:

SELECT
   t.login_name
   ,MAX(t.login_time) AS latest_login_time
FROM this_table AS t
INNER JOIN (
   -- Determine who has the most logins
   SELECT TOP 1 x.login_name, COUNT(x.id) AS login_count
   FROM this_table AS x
   GROUP BY x.login_name
   ORDER BY login_count DESC  -- to get the highest counts first
) AS m
   ON t.login_name = m.login_name
GROUP BY t.login_name
;

That gets you one name and date, and that's it, though it doesn't take into account the possibility that there could be more than 1 name with the maximum number of logins. I'll leave that up to you to figure out.