List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 21 1999 9:35am
Subject:Problem with DISTINCT
View as plain text  
>>>>> "sal" == sal  <sal@stripped> writes:

>> Description:
sal> I have a table with an auto_increment primary key, starts at 1.  There is also a
> row for each 'product' that contains a size and color, so there may  be multiple product
> id's but with distinct color/size combinations.

sal> If I do a simple

sal> SELECT DISTINCT Color FROM Products WHERE productId='ABCDEF'

sal> The rows are returned with distinct info for the Colors and are sorted by the
> colors.  My primary key is called 'id'.

sal> When I do a

sal> SELECT DISTINCT Color FROM Products WHERE productId='ABCDEF' order by id;

sal> I no longer get distinct results for the Color field.  I thought it might be a
> problem with the primary key handling, so I added a new column called display, which is
> basically equal to id, and tried ordering by that.  It did the same thing.  Returned all
> Colors, including duplicates.

sal> Is my SQL knowledge to blame here or is this a real bug?

Both.

In SQL you are not allowed to do an ORDER by something not in the
SELECT part;  MySQL fixes this by adding an hidden column, but this
will be of be noticed by the DISTINCT.

Anyway; The above works as you want in MySQL 3.23 (even if I think
that in this case your query is not very logical, as the query may
have many different correct sorting orders, depending on from which
row the 'id' is taken)

Regards,
Monty
Thread
Problem with DISTINCTsal20 Oct
  • Re: Problem with DISTINCTsinisa21 Oct
  • Problem with DISTINCTMichael Widenius23 Oct