CODEWITHSUNDEEP
mysqltimestamp
Gabcy
Gabcy

Reputation: 23

timestamp gives false result

I have a problem while working on my project with the timestamps. I've made a simple database to illustrate the problem:

id  timestamp           status
1   2014-01-14 14:14:29 old
2   2014-01-14 14:04:35 new
1   2014-01-14 14:15:19 new

I've ran the following query: 

SELECT MAX( TIMESTAMP ) AS TIMESTAMP, id, 
STATUS FROM  `proba` 
GROUP BY id

The result is:

TIMESTAMP           id  STATUS

2014-01-14 14:15:19 1   old

2014-01-14 14:04:35 2   new

I got the right timestamp, but the wrong line of data (if i add multiple rows, i'll get the first row always).

I use phpMyAdmin - 2.11.4 if it does matter :)

What can i do about this? I need the most recent line (in the exact problem, i'll need a group by id too).

Thank you!

Upvotes: 2

Views: 54

Answers (1)

NewInTheBusiness
NewInTheBusiness

Reputation: 1475

SELECT stamp, p1.id,  p1.STATUS FROM `proba` p1 
INNER JOIN (
   SELECT MAX(timestamp) AS stamp FROM `proba` GROUP BY id 
) p2 ON p1.timestamp = p2.stamp

Upvotes: 1

Related Questions