0

I use this query in PHPmyAdmin, i have date that contain(date,hour),supp, price and code_product (it repeated many times) :

I want to return the last price for the last date of product code

   SELECT invent.code_product,products.supp,products.price,products.date  
   FROM products

   LEFT JOIN list_product ON products.supp = list_product.supp
   LEFT JOIN invent on list_product.id_product = invent.fk_product

   WHERE invent.code_product= "XXX185"
   ORDER BY products.date  DESC
   LIMIT 1 ;

This is the result for one code :

enter image description here

The query works for one code and limit it the result in 1.

How can i use the same query to return the last date and price for all codes?

7
  • Please clarify and offer a sample of data that you want to select. As it is now, your question is unclear. How many code products do you want to have associated with your MAX(date)? what rules? and similar questions.
    – RLF
    Jul 23, 2015 at 14:30
  • @RLF I have code_product that is repeated multiple times with different dates i want to return all code_product with their last date.
    – Sarah
    Jul 23, 2015 at 14:39
  • Do you want to return the MAX(date) for every code_product or for which one?
    – oNare
    Jul 23, 2015 at 15:01
  • @oNare i have grouped by code_product to eliminate the mutiple code and I want to retrieve the last date of each code in my table.
    – Sarah
    Jul 23, 2015 at 15:10
  • 1
    Edit the question and add the SHOW CREATE TABLE ...; output for all 3 tables. Jul 24, 2015 at 10:34

2 Answers 2

2

It sounds like you want something like this:

SELECT invent.code_product,products.supp,products.price, 
       MAX(products.date) AS Date
FROM products 
LEFT JOIN list_product ON products.supp = list_product.supp
LEFT JOIN invent on list_product.id_product = invent.fk_product
Group by  invent.code_product,products.supp,products.price
Order by  products.date

To fully understand your comments on Date and Hour, it would be good to see your data definition and some representative data.

1
  • yeah i added my data.
    – Sarah
    Jul 24, 2015 at 7:56
1

If you are expecting a single latest record per product code,
then sort them first and filter for the expected results.

Example:

SELECT product_code, product_supplier, product_price, product_date
  FROM (
        SELECT CASE WHEN @prev != ( @curr := i.code_product ) 
                         THEN 1 
                    ELSE 0 
                END AS latest
             , @prev:=@curr AS product_code
             , p.supp AS product_supplier
             , p.price AS product_price
             , p.date AS product_date
          FROM products AS p
          LEFT JOIN list_product AS l ON p.supp = l.supp
          LEFT JOIN invent AS i ON l.id_product = i.fk_product
          JOIN ( SELECT @prev:='', @curr:='' ) AS initializer
         ORDER BY product_code ASC, product_date DESC
        ) AS scrutinized
 WHERE latest = 1
3
  • i want the last price for the last date of product code.
    – Sarah
    Jul 24, 2015 at 13:24
  • it dosen't work this query.
    – Sarah
    Jul 24, 2015 at 15:37
  • @Sarah: What is meant by it doesn't work... ??? What were expecting and what was the result. Add some sample data to your question along with expected output. Jul 27, 2015 at 6:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.