Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Mar 2012
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Mysql query newest entry for each idproduct?

    PHP Code:
               $query =        ("SELECT * 
                            FROM operators
                            JOIN products
                            ON operators.idoperators = products.idoperator JOIN results
                            ON products.idProducts = results.idproducts
                            WHERE (products.idProducts ='2'
                            OR products.idProducts ='5'
                            OR products.idProducts ='9'
                            OR products.idProducts ='10'
                            OR products.idProducts ='12'
                            OR products.idProducts ='13')
                            ORDER BY date  DESC
                            LIMIT 6"
    ); 
    I have the following query that lists the latest 6 rows but the problem is idproduct 9 has new rows inserted more regularly so it means that one of the products wont be listed.

    How can I only show the newest row for each idProduct in the WHERE statement?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,595
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    It's not easy. You have to create a UNION of 6 separate SELECTs, one for each idProducts.

    You are doing SELECT * (which is a really bad idea) and you don't show what table date is from, so I can't tell you the exact syntax to use.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •