SQL: finding double entries without losing the ID

Question

I have 1 table "Products" that looks like this:

ID     Product     Shop   Color
01     Car         A      Black
02     Car         B      Black
03     Bike        C      Red
04     Plane       A      Silver
05     Car         C      Black
06     Bike        A      Red

In this example, a Product always has the same color, independent from the Shop where it is sold. I want to make a query, that returns a distinct set of products, with the Color property. I also will need to have an ID, it could be any ID, that allows me to do a follow up query.

The result of the query should be:

ID     Product     Color
01     Car         Black
03     Bike        Red
04     Plane       Silver

I tried:

SELECT DISTINCT 
  Product, Color 
FROM 
  Products

But that obviously doesn't return the ID as well

I guess I need to join something, but my knowledge of SQL is too poor. I hope this is something simple.

Answer 1

This would be one way of getting the result you want:

SELECT min(ID), Product, Color FROM table GROUP BY Product, Color;

Answer 2

How About

SELECT 
   Product, Color, Min(ID)
FROM 
   TABLE
GROUP BY
   Product, Colour

That'll return unique Product/Color Combinations and the first (lowest) ID found.

Answer 3

You need to use the GROUP BY clause.

The same but obtaining the maximun ID:

SELECT MAX(ID) AS ID, Product, Color
FROM Products
GROUP BY Product, Color
ORDER BY ID

Answer 4

Thank you guys! It's incredible how fast you answered my question. I hope I know something that could be of use to you.