order by max date desc

hi  - this is what i'm trying to accomplish:

I want to select the rows with the most recent date for a given item_id.

The result should be:

inventory_date  inventory_count  item_id
2009-01-07       54               1
2008-12-31       65               2
2009-01-02       36               3

here's a pic of the table


the column names are wrong but will this logic work??
  $result = mysql_query('SELECT `http_referer`, COUNT(*) AS count, MAX(`date_requested`) AS last_request_date FROM `stats` WHERE `http_referer` IS NOT NULL GROUP BY `http_referer` ORDER BY MAX(`date_requested`) DESC');
Who is Participating?
Try this out....(replace yourtable with your table name)
SELECT * FROM  yourtable s1 WHERE  inventory_date=(SELECT MAX(s2.inventory_date) FROM yourtable s2 WHERE s1.item_id = s2.item_id)

Open in new window

You can try the following query. This way it will give you (replace thetable with your table name and yourid with the id you wan to filter with):
SELECT * FROM  thetable T WHERE  T.inventory_date=(SELECT MAX(TT.inventory_date) FROM thetable TT WHERE T.item_id = TT.item_id) AND T.item_id=yourid

Open in new window

phillystyle123Author Commented:
that's what i was looking for  - thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.