当我在mySQL中按列分组时如何获取最后插入的行？

I run this query to get me the active users in a duration of 10 minutes. the clicks table has multiple rows of a user with each page. I can get the list of user but the page next to the output is not the last row, but the first.

我运行此查询以在10分钟内让我成为活跃用户。点击表具有每个页面的多个用户行。我可以获取用户列表,但输出旁边的页面不是最后一行,而是第一行。

That means if 4 rows are stored for a specific user, 1st is 1 minute ago, and the 4th is 8 minutes ago, it will show me the 8th minute page, not the 1st minute page.

这意味着如果为特定用户存储了4行,第1行是1分钟前,第4行是8分钟前,它将显示第8分钟页面,而不是第1分钟页面。

How to fix this ?

如何解决这个问题?

SELECT user
     , page 
  FROM clicks 
 WHERE timestamp >= NOW() - INTERVAL 10 MINUTE 
 GROUP 
    BY user
 ORDER 
    BY id DESC

3 个解决方案

#1

Try this:

 select temp.*
from
(
 select user , page
 from clicks
 order by timestamp desc
) temp

group by temp.user;

#2

mysql_insert_id Retrieves the ID generated for an AUTO_INCREMENT column by the previous query

mysql_insert_id检索上一个查询为AUTO_INCREMENT列生成的ID

#3

This will work for all versions

这适用于所有版本

SELECT 
    id, tc_stage_id, user
FROM
    clicks
WHERE
    id IN (SELECT 
        MAX(id)
    FROM
        clicks
    GROUP BY user)

#1