How can i return the date of max id?

Question

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:

But the value that's must return:

09/07/2015 for id = 152752

Thanks for your help.

Answer 1

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 ;

Answer 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')

Answer 3

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>