How to Retrieve the Latest Login Date for Each User in SQL?

SQL query the last login record date of each user

Question:

Assuming the database table contains user login information, how do we retrieve the last login date for each user? Consider the possibility of duplicate login dates.

Solution:

There are two common ways to solve this problem:

Method 1: Using INNER JOIN subquery

This method utilizes a subquery to identify the last login date of each user, which is then used to filter the main table:

<code class="language-sql">select t.username, t.date, t.value
from MyTable t
inner join (
    select username, max(date) as MaxDate
    from MyTable
    group by username
) tm on t.username = tm.username and t.date = tm.MaxDate</code>

Method 2: Window function

For databases that support window functions, a more efficient method can be used:

<code class="language-sql">select x.username, x.date, x.value 
from (
    select username, date, value,
        row_number() over (partition by username order by date desc) as _rn
    from MyTable 
) x
where x._rn = 1</code>

This method uses row number sorting on the partitioned data, where each user's last date is assigned rank 1.

The above is the detailed content of How to Retrieve the Latest Login Date for Each User in SQL?. For more information, please follow other related articles on the PHP Chinese website!