1

I want to select the max id_product and date that corresponds to this max id. my problem is that it returns the max id_ product with a different date.

I can't use the max(date) because it will return always the max what i need is the date of this max id.

This my query :

 SELECT 
   MAX(id_product),
   `date` 
 FROM `products` 
 WHERE `fk_stat` = '1' and `id_order` = '77185' ;

The result:

enter image description here

But the value that's must return:

09/07/2015 for id = 152752

enter image description here

Thanks for your help.

  • 1
    Next time try adding inserts as actual text statemenst, or prepare a fiddle – jkavalik Jul 21 '15 at 11:37
  • @jkavalik i added it. – Sarah Jul 24 '15 at 9:46
2

You don't need a subquery for this. It can be simple and more efficient:

SELECT id_product, date  
FROM products
WHERE fk_stat = 1
  AND id_order = 77185 
ORDER BY id_product DESC
LIMIT 1 ;
  • That was my first approach, she said it didn't work for her. Lol – oNare Jul 21 '15 at 11:51
  • @oNare, no, your first query had MAX(date) and it did not have LIMIT. – ypercubeᵀᴹ Jul 21 '15 at 11:53
  • @oNare what u wrote to me it did not work – Sarah Jul 21 '15 at 12:06
  • @ypercube i want to use the same query for another problem it's works if i specify the id in where with limit 1 i if want to use it for all data it dosen't work. – Sarah Jul 24 '15 at 10:06
  • @Sarah, I have no idea what you are asking. – ypercubeᵀᴹ Jul 24 '15 at 10:16
2

Do it with a subquery,assuming unique values on id_product

SELECT id_product,date  FROM products 
WHERE id_product IN
(SELECT MAX(id_product) as maxid FROM products WHERE    
fk_stat = '1' and id_order = '77185')
-1

Try using MAX(date) from products and make a JOIN on the same table to bring the MAX(id_product):

SELECT 
    MAX(p2.id_product) as id_product,
    MAX(p.date) as date 
FROM test.products as p
JOIN test.products AS p2 ON (p2.fk_stat=p.fk_stat AND p2.id_order=p.id_order)
WHERE p.fk_stat = '1' and p.id_order = '77185';

Test:

mysql> SELECT * from test.products;
+------------+---------------------+---------+----------+
| id_product | date                | fk_stat | id_order |
+------------+---------------------+---------+----------+
|          1 | 2015-06-10 00:00:00 |       2 |    77184 |
|          2 | 2015-07-20 00:00:00 |       1 |    77185 |
|          3 | 2015-07-20 00:00:00 |       1 |    77185 |
|          4 | 2015-07-20 00:00:00 |       1 |    77185 |
+------------+---------------------+---------+----------+
4 rows in set (0.00 sec)

mysql> SELECT MAX(p2.id_product) as id_product,MAX(p.date) as date FROM test.products as p JOIN test.products AS p2 ON (p2.fk_stat=p.fk_stat AND p2.id_order=p.id_order) WHERE p.fk_stat = '1' and p.id_order = '77185';
+------------+---------------------+
| id_product | date                |
+------------+---------------------+
|          4 | 2015-07-20 00:00:00 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql> 
  • no it returns the last date "09/07/2015" but not for max id_product – Sarah Jul 21 '15 at 11:25
  • Can you add some inserts test values? – oNare Jul 21 '15 at 11:26
  • 1
    mixing normal and aggregated fields like this is bad and won't do what you want – jkavalik Jul 21 '15 at 11:33
  • @oNare i added a insert values. – Sarah Jul 21 '15 at 11:35
  • @Sarah try the last query, I edited it. – oNare Jul 21 '15 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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