order by max date desc

Posted on 2009-12-20
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
    LVL 4

    Accepted Solution

    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

    LVL 4

    Expert Comment

    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

    that's what i was looking for  - thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
    Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    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 …

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now