MYSQL get latest job number

Question

I have this table:

TableNumber(Int 0 to 25)|Name(varchar 100)|Project(varchar 15)|StartDate(Datetime)
1                       |David            |P1                 |'2015-02-06 08:00:00'    
2                       |Sebastien        |P2                 |'2015-02-06 08:00:00'    
1                       |David            |P4                 |'2015-02-06 12:00:00'    
2                       |Sebastien        |P3                 |'2015-02-07 08:00:00'

And I am looking to get the latest job for each person on a set day.

I would like to have:

TableNumber(Int 0 to 25)|Name(varchar 100)|Project(varchar 15)|StartDate(Datetime)    
2                       |Sebastien        |P2                 |'2015-02-06 08:00:00'    
1                       |David            |P4                 |'2015-02-06 12:00:00'   

So I want to exclude P3 since its not '2015-02-06' and I want to exclude P1 cause its not the latest job for David (its P4).

Please consider that NOW() returns '2015-02-06 15:00:00' in the following exemples.

Here is what I tried:

SELECT * FROM MyTable WHERE DATEDIFF(startdate, NOW()) = 0 ORDER BY tablenum DESC;

But this only excluded P3

So I tried this instead:

SELECT * FROM MyTable AS p WHERE DATEDIFF(p.startdate, NOW()) = 0 AND TIMEDIFF(p.startdate, NOW()) = (SELECT MAX(TIMEDIFF(p2.startdate, NOW())) FROM MyTable AS p2 WHERE p2.startdate = p.startdate) ORDER BY tablenum DESC;

But Its still doesn't exclude P1.

Anyone know how I could achieve this? BTW startdate will always be a round hour (08:00:00 or 12:00:00 or 22:00:00...)

UPDATE

Yeah since it wasn't very clear what I wanted I will clarify here:

I need to know the last project worked on by every person.

so in my table I need to know that Sebastien work on P2 on table number 2 and that David work on P4 on table number 1. I don't want P1 because its not the last project that David worked on (by last project I also include the project he is working on right now). I also want to rule out everything in the future so P3 (who is tomorrow) must not display.

Answer 1

The following query will provide you with the date/time of the earliest job for a given name on a given day. In the following example I assumed you want the earliest jobs of each user on 2015-02-06.

SELECT Name, min(StartDate)
FROM MyTable
WHERE StartDate >= '2015-02-06'
  AND StartDate < '2015-02-07'
GROUP BY Name

Using the above query, you can trivially get the final solution:

SELECT t1.project, t2.name, t2.StartDate
FROM MyTable t1 INNER JOIN
   (SELECT Name, min(StartDate)
FROM MyTable
WHERE StartDate >= '2015-02-06'
  AND StartDate < '2015-02-07'
GROUP BY Name) t2 ON t1.Name = t2.Name AND t1.StartDate = t2.StartDate