MySQL with a date range and selection of only the most recent records for each identifier

Question

I have the following table:

Record ID Status Date Timestamp ---------------------------------------------------------- 1 1 waiting 2010-02-02 2010-02-02 12:00:00 2 1 finished 2010-02-02 2010-02-02 12:30:00 3 2 waiting 2009-02-02 2009-02-02 12:00:00 

I want to get records where Date is between 2010-01-01 and 2010-03-03. (this should give me records 1 and 2)

Next, I want to get only the last (with the most recent timestamp) for each identifier. (this should only give me entry 2).

I am not sure how I need to structure my query. I managed to create the following query:

 SELECT `Record` `ID`, MAX( `Timestamp` ) FROM `myTable` WHERE `Date` BETWEEN '2010-01-01' AND '2011-03-03' GROUP BY `ID` 

The problem with the above query is that for some reason it gives me the following result:

 Record ID Timestamp --------------------------------- 1 1 2010-02-02 12:30:00 

which is correct, except that the Record field must have a value of 2, not 1.

Answer 1

 SELECT t1.* FROM `myTable` t1 INNER JOIN ( SELECT MAX(`Timestamp`) as Timestamp FROM `myTable` WHERE `Date` BETWEEN '2010-01-01' AND '2011-03-03' GROUP BY `ID` ) t2 ON t1.Timestamp = t2.Timestamp 

Answer 2

Try the following query

 SELECT `Record` `ID`,Timestamp FROM `myTable` WHERE `Date` BETWEEN '2010-01-01' AND '2011-03-03' Order by `Timestamp` desc limit 1