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

http://www.microshell.com/wp-content/uploads/2009/01/optimize_max_min_inventory_table.jpg

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');
phillystyle123Asked:
Who is Participating?
 
me655321Commented:
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

0
 
72lionsCommented:
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

0
 
phillystyle123Author Commented:
that's what i was looking for  - thanks!
0
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.