>>>>> "sal" == sal <sal@stripped> writes:
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?
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)