List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 5 1999 7:33pm
Subject:DISTINCT does not seem to work with ALIASES and ORDER BY
View as plain text  
>>>>> "brian" == brian  <brian@stripped> writes:

brian> Description:
brian> If I use ORDER BY on a SELECT DISTINCT using ALIASES I get all the rows
> returned, and not the distinct ones.

brian> How-To-Repeat:

brian> The following statement works...

brian> SELECT DISTINCT YEAR(date) as year, MONTHNAME(date), date as month
brian> FROM news;

brian> But the following statement does not work correctly (No longer get distinct
> rows)...

brian> SELECT DISTINCT YEAR(date) as year, MONTHNAME(date), date as month
brian> FROM news
brian> ORDER BY date;

brian> Fix:

brian> Sorry, couldn't figure out a fix...

Hi

The problem is that your use of 'ORDER BY date' adds a hidden date
field to the query (that also needs to be unique).
Are you sure this isn't a problem ?

If not, please mail us a complete example that shows this problem
(Your SELECT DISTINCT example doesn't look right to me, as you use
your 'date' as a 'month'!)

Regards,
Monty
Thread
DISTINCT does not seem to work with ALIASES and ORDER BYbrian1 Oct
  • DISTINCT does not seem to work with ALIASES and ORDER BYMichael Widenius5 Oct