CODEWITHSUNDEEP
phpmysql
Zibar
Zibar

Reputation: 95

Select by latest timestamp

I have a following table :

+-------+--------+---------------------+
| score |  user  |      scrap_time     |
+-------+--------+---------------------+
|  200  |  Bob   | 2015-09-08 11:46:17 |
+-------+--------+---------------------+
|  210  |  Alice | 2015-09-08 11:46:17 |
+-------+--------+---------------------+
|  240  |  Bob   | 2015-09-08 11:48:00 |
+-------+--------+---------------------+
|  260  |  Alice | 2015-09-08 11:48:01 |
+-------+--------+---------------------+

I want to get all the scores for each user that have the latest scrap_time and ignore the older ones.

Example:

+-------+--------+---------------------+
| score |  user  |      scrap_time     |
+-------+--------+---------------------+
|  240  |  Bob   | 2015-09-08 11:48:00 |
+-------+--------+---------------------+
|  260  |  Alice | 2015-09-08 11:48:01 |
+-------+--------+---------------------+

I have been trying to come up with a query, like this one :

select * from scores where date(scrap_time) = ( select max(scrap_time) from scores);

But this does not give me the results I need.

Upvotes: 2

Views: 64

Answers (3)

Hirvita Shah
Hirvita Shah

Reputation: 41

use max function with group by which will provide u recent time of each score

 select   score, user, max(scrap_time) 
 from     scores  
 group by score;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You need to get the MAX(scrap_time) per user, then simply join back to your table to get the required result set. Something like the following should work:

SELECT s.score, s.user, s.scrap_time 
FROM scores AS s
INNER JOIN (SELECT user, max(scrap_time)  AS maxTime
            FROM scores
            GROUP BY user) AS t
ON s.user = t.user AND s.scrap_time = maxTime

Demo here

Upvotes: 2

vhu
vhu

Reputation: 12788

First you need to find MAX(scrap_time) per user and then find those rows

SELECT * FROM scores WHERE (user,scrap_time) IN (
 SELECT user,MAX(scrap_time) scrap_time FROM scores
 GROUP BY user);

Upvotes: 2

Related Questions