CODEWITHSUNDEEP
mysql
Xyz
Xyz

Reputation: 167

Pulling latest values using distinct and max

I have a table that looks like this if I 'select *'

+----+--------+------+------------+
| id | name   | task | day        |
+----+--------+------+------------+
|  1 | Rodney | 2    | 2016-05-05 |
|  2 | Rodney | 2    | 2016-05-08 |
|  3 | Rodney | 8    | 2016-05-08 |
|  4 | Scott  | 2    | 2016-05-05 |
|  5 | Scott  | 8    | 2016-05-05 |
|  6 | Frank  | 2    | 2016-05-05 |
|  7 | Frank  | 2    | 2016-05-08 |
+----+--------+------+------------+

What I'm trying to achive is a query that will get the last entered 'task' for each person. So, in this case I would want back:

 2 | Rodney | 2    | 2016-05-08 
 3 | Rodney | 8    | 2016-05-08
 4 | Scott  | 2    | 2016-05-05
 5 | Scott  | 8    | 2016-05-05
 7 | Frank  | 2    | 2016-05-08

I'm pretty sure I need to use distinct against name & task and max for the most recent entry. Just not sure how to structure the two of them together to get the result.

select distinct name, task from test;

Gets me close...

+--------+------+
| name   | task |
+--------+------+
| Rodney | 2    |
| Rodney | 8    |
| Scott  | 2    |
| Scott  | 8    |
| Frank  | 2    |
+--------+------+

But no date...My SQL is limited. Any help would be appreciated.

Upvotes: 0

Views: 38

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Aggregate your rows so as to get the latest day per name. Then access the table again to get the records matching thse days:

select *
from test
where (name, day) in
(
  select name, max(day)
  from test
  group by name
);

Another way is to select the records for which not exists a later record for the same name:

select *
from test
where not exists
(
  select *
  from test later
  where later.name = test.name
  and later.day > test.day
);

Upvotes: 1

Related Questions