CODEWITHSUNDEEP
mysql
John Nguyen
John Nguyen

Reputation: 41

mysql select latest record only for record

I have a MySQL database with the following structure:

custodian | counta | countc | countc | total | date
-------------------------------------------------------
ed        | 1      | 2      | 3      | 6     | 1/1/2016
ed        | 2      | 3      | 5      | 10    | 1/2/2016
ed        | 2      | 3      | 6      | 11    | 1/3/2016
ed        | 1      | 3      | 5      | 9     | 1/4/2016
fred      | 1      | 2      | 3      | 6     | 1/1/2016
fred      | 2      | 3      | 5      | 10    | 1/2/2016
fred      | 2      | 3      | 6      | 11    | 1/3/2016
fred      | 1      | 3      | 5      | 9     | 1/4/2016

How do I return the latest record for a custodian? I've been playing around with this condition where date >= DATE_SUB(NOW(),INTERVAL 59 MINUTE) since the table is updated hourly, but if I update the script twice in an hour, I would return more than one result per custodian.

Any advice?

Upvotes: 2

Views: 31

Answers (2)

worldofjr
worldofjr

Reputation: 3886

You could try this

SELECT * FROM tbl ORDER BY date DESC LIMIT 1

The most recent date will be the first record when ordered decendingly, and limiting the select to 1 means you get only the latest record.

Upvotes: 0

elixenide
elixenide

Reputation: 44851

You need to combine ORDER BY and LIMIT:

SELECT *
FROM yourTableName
WHERE custodian = 123
ORDER BY `date` DESC
LIMIT 1

Upvotes: 2

Related Questions