Learn how to a build a cloud-first strategyRegister Now


order by max date desc

Posted on 2009-12-20
Medium Priority
Last Modified: 2012-05-08
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');
Question by:phillystyle123

Accepted Solution

me655321 earned 2000 total points
ID: 26093209
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


Expert Comment

ID: 26094404
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


Author Closing Comment

ID: 31668360
that's what i was looking for  - thanks!

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month20 days, 21 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question