CODEWITHSUNDEEP
phpmysql
poulokopri
poulokopri

Reputation: 75

How to get the last inserted row when I Group by column in 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.

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.

How to fix this ?

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

Upvotes: 1

Views: 2155

Answers (3)

Jay Momaya
Jay Momaya

Reputation: 2049

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)

Upvotes: 5

Sagar Joon
Sagar Joon

Reputation: 1417

Try this:

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

group by temp.user;

Upvotes: 1

Dioni Butter
Dioni Butter

Reputation: 169

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

Upvotes: 1

Related Questions