CODEWITHSUNDEEP
mysql
Nerden
Nerden

Reputation: 41

MySQL Query issue.show latest date for unique entries

here is a simplified version of my database

ID#
Date (Datetime)
Customer (Varchar)
Item (Varchar)

Each customer can have multiple items and every row will have a unique datetime stamp.

I am trying to write a query to show the latest entry for each item attached belonging to each Customer

If that doesn't make sense this might be better:

For instance data.

1   1/1/12 00:00:00   Cust1  Item1 
2   2/1/12 00:00:00   Cust1  Item1 
3   3/1/12 00:00:00   Cust1  Item2 
4   2/1/12 00:00:00   Cust2  Item1 
5   3/1/12 00:00:00   Cust2  Item1 
6   4/1/12 00:00:00   Cust2  Item1

Should display

2   2/1/12 00:00:00   Cust1  Item1 
3   3/1/12 00:00:00   Cust1  Item2 
6   4/1/12 00:00:00   Cust2  Item1

Thanks in advance for any help

Upvotes: 0

Views: 210

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

You can do:

SELECT a.*
FROM   tbl a
JOIN   (
       SELECT MAX(ID) AS ID FROM tbl GROUP BY Customer, Item
       ) b ON a.ID = b.ID

This gets the highest ID per customer (assuming it's an auto-incremented value and the highest value is also the latest), and since it's unique, we simply join the same table on the ID column, which will only return the latest row per customer.

Upvotes: 1

Related Questions