How to Efficiently Retrieve the Latest User Activity Record with SQL?

Use SQL to efficiently obtain the latest user activity records

This article explores how to query the database through SQL to obtain the latest activity record date of each user.

Suppose we have a database table that records user login events:

用户名	日期	数值
brad	2010-01-02	1.1
fred	2010-01-03	1.0
bob	2009-08-04	1.5
brad	2010-02-02	1.2
fred	2009-12-02	1.3

Easy method:

A straightforward approach is to use an inner join to match usernames and latest dates:

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

However, this approach may suffer from duplicate records with the same date.

Use window functions:

In order to solve the problem of duplicate records, you can use window functions:

<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 query utilizes row partitioning and the ROW_NUMBER() function to assign a rank to each record for each user, with the highest rank indicating the latest record. The WHERE clause ensures that only records with rank 1, representing the most recent activity for each user, are selected.

The above is the detailed content of How to Efficiently Retrieve the Latest User Activity Record with SQL?. For more information, please follow other related articles on the PHP Chinese website!