CODEWITHSUNDEEP
mysql
Marcel
Marcel

Reputation: 49

Mysql query latest entry with multiple group by

right now I'm using the following simple query which shows the history of training progress per user: 

SELECT user_id, date, project, status
FROM a_training_status
GROUP by user_id, project

This way I can see when people completed their training and when status has changed. But I would also need a table which shows only the lastest status per rater and project. I tried with select max(date) but the status column does not match.

Is there a way to have two group by and only show the latest?

Upvotes: 0

Views: 34

Answers (2)

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Use MySQL MAX

SELECT *
FROM a_training_status t1
Where date=(SELECT MAX(t2.date)
              FROM a_training_status t2
              WHERE t1.user_id = t2.user_id AND t1.project = t2.project)

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

SELECT user_id, project, status
FROM a_training_status AS t1
WEHRE `date` = (SELECT MAX(`date`) 
                FROM a_training_status AS t2
                WHERE t1.user_id = t2.user_id AND t1.project = t2.project)

Upvotes: 1

Related Questions