CODEWITHSUNDEEP
mysqldatetimegroup-by
ClydeFrog
ClydeFrog

Reputation: 1020

SELECT statement to get latest row

I've been assigned to do further development on a website which is, at this moment, a site for managers of convenience stores to monitor what articles that's inside the "grocery bag of the week". The former developer did very sloppy work so whenever the managers think: "Damn! I need to edit this article because I just entered the wrong value of it". They have to enter a new article with the same name and then they have two articles by the same name but with different price and DATE_TIME (the date and time when the article was entered). The database looks something like this:

________________________________________________
|___itemId___|___price___|______date_time______|
|     1      |    2.30   | 2012-01-01 00:00:00 |
|     2      |    1.50   | 2012-01-01 00:00:00 |
|     3      |    5.99   | 2012-01-01 00:00:00 |
|     4      |    0.99   | 2012-01-01 00:00:00 |
|     4      |    2.00   | 2012-01-01 00:10:00 |
|     4      |    4.99   | 2012-01-01 00:20:00 |
|     5      |   10.00   | 2012-01-01 00:00:00 |
|     6      |   39.99   | 2012-01-01 00:00:00 |

I was working on this SELECT itemId, price, MAX(date_time) FROM myTable to get the latest row of every item like this:

________________________________________________
|___itemId___|___price___|______date_time______|
|     1      |    2.30   | 2012-01-01 00:00:00 |
|     2      |    1.50   | 2012-01-01 00:00:00 |
|     3      |    5.99   | 2012-01-01 00:00:00 |
|     4      |    4.99   | 2012-01-01 00:20:00 |
|     5      |   10.00   | 2012-01-01 00:00:00 |
|     6      |   39.99   | 2012-01-01 00:00:00 |

But all i get is:

________________________________________________
|___itemId___|___price___|______date_time______|
|     4      |    4.99   | 2012-01-01 00:20:00 |

Can someone please help me on this matter?

Upvotes: 1

Views: 405

Answers (3)

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20775

SELECT t0.itemId, 
       t0.price, 
       t0.date_time 
FROM   myTable AS t0 
       LEFT JOIN myTable AS t1 
         ON t0.itemId = t1.itemId 
            AND t1.date_time > t0.date_time 
WHERE  t1.itemId IS NULL;

Upvotes: 1

Addicted
Addicted

Reputation: 1704

try using either group by or distinct

Upvotes: 1

adrien
adrien

Reputation: 4439

Add a GROUP BY clause:

SELECT itemId, price, MAX(date_time)
FROM myTable 
GROUP BY itemId

Upvotes: 2

Related Questions