>>>>> "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
