Tutorial :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.


Solution:1

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

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


Solution: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.


Solution: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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »