Select unique product id and order by stock id desc

Question

I'm trying to get unique product from stock...

stock table:

id - product - quantity
1    2            0
2    3            5
3    2            19
4    4            3
5    2            8

result

id - product - quantity
5    2            8
4    4            3
2    3            5

it's working with

SELECT max(id) as id, 
product
FROM stock 
GROUP by product 
ORDER by id DESC

but I can't get last quantity of product with this query I get:

id - product - quantity
1    2            0
2    3            5
4    4            3

I need latest quantity of the product.

Answer 1

You can wrap your existing query in a subquery and join that on the table itself so you can get the other columns of the same row.

SELECT  a.*
FROM    stock a
        INNER JOIN
        (
            SELECT  product, MAX(ID) id
            FROM    stock
            GROUP   BY product
        ) b ON  a.product = b.product
                AND a.ID = b.ID
ORDER   BY a.id DESC