SQL query distinct with additional select

Question

I need to perform a query in MySQL that returns distinct values for product_id but also I need to select and return 'id' field which is in that particular table.

This query will return distinct product_id's without id:

SELECT DISTINCT product_id FROM orders_cart

This query will use distinct on both fields which and I want to use it on product_id and see the id

SELECT DISTINCT id, product_id FROM orders_cart

It would be quite easy to do on pgsql but I have no idea how to do this on mysql.

Answer 1

Thats my final code: The most important bits for this issue were line 1,2 and 4 :) GROUP BY did the trick :)

SELECT orders_cart.id, product_id, order_id 
FROM orders_cart 
LEFT JOIN orders_order ON orders_cart.order_id=orders_order.id
WHERE orders_order.status='Wysłano'
GROUP BY orders_cart.product_id

Answer 2

Your query is not well-defined: Consider this table

id   product_id
1     1
2     2
3     1
4     2

What should your query result be? If you mean

id        product_id
1 or 3     1
2 or 4     2

you are in the land of non-deterministic queries.

What you could do is

SELECT MIN(id), product_id FROM orders_cart GROUP BY product_id

which would deterministically produce

id   product_id
1     1
2     2