Here is the database table
╔════╦═════════════╦══════════════════╦═══════╗
║ id ║ customer_id ║ last_seen ║ param ║
╠════╬═════════════╬══════════════════╬═══════╣
║ 1 ║ 12345 ║ 2012-08-01 12:00 ║ 1 ║
║ 2 ║ 22345 ║ 2012-08-01 12:00 ║ 1 ║
║ 3 ║ 32345 ║ 2012-08-01 12:00 ║ 1 ║
║ 4 ║ 42345 ║ 2012-08-01 12:00 ║ 1 ║
║ 5 ║ 52345 ║ 2012-08-01 12:00 ║ 1 ║
║ 6 ║ 12345 ║ 2012-09-01 12:00 ║ 2 ║
║ 7 ║ 12345 ║ 2012-10-01 12:00 ║ 3 ║
╚════╩═════════════╩══════════════════╩═══════╝
where id is an AUTO INCREMENT primary key.
What I want to achieve is to get the last record of each customer_id. Expected Result :
╔════╦═════════════╦══════════════════╦═══════╗
║ id ║ customer_id ║ last_seen ║ param ║
╠════╬═════════════╬══════════════════╬═══════╣
║ 2 ║ 22345 ║ 2012-08-01 12:00 ║ 1 ║
║ 3 ║ 32345 ║ 2012-08-01 12:00 ║ 1 ║
║ 4 ║ 42345 ║ 2012-08-01 12:00 ║ 1 ║
║ 5 ║ 52345 ║ 2012-08-01 12:00 ║ 1 ║
║ 7 ║ 12345 ║ 2012-10-01 12:00 ║ 3 ║
╚════╩═════════════╩══════════════════╩═══════╝
I tried this SQL, but it returns incorrect result :
SELECT customer_id, param, last_seen
FROM `my_table`
GROUP BY customer_id
ORDER BY last_seen DESC
What am I missing here ?
UPDATE: Table structure ( output of DESC my_table )
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| customer_id | varchar(8) | NO | | NULL | |
| last_seen | datetime | NO | | NULL | |
| param | int(11) | NO | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
The idea behind the subquery is that it separately gets the latest last_seen value for each Customer_ID. The result of the subquery is then joined with the original table provided that the condition must be met: the CustomerID and the dates must match with each other.
SELECT a.*
FROM Customer a
INNER JOIN
(
SELECT Customer_ID, MAX(last_seen) maxDate
FROM Customer
GROUP BY Customer_ID
) b ON a.Customer_ID = b.Customer_ID AND
a.last_seen = b.maxDate
ORDER BY a.ID
Follow-up Question: can you please check if the the last_seen value of record id 6 is correct?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With