List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 20 1999 1:14pm
Subject:Re: select !distinct! does not work when using functions?
View as plain text  
Holger Ebel wrote:
> 
> Hi,
> 
> i expect that the following should work (under 3.22.22):
> 
> select distinct DATE_FORMAT(datum, '%d.%m.%Y') from Info_lnk order by
> datum; (where datum is a timestamp(14) field)
> 
> and give me a result with the unique data like:
> 
> +----------+
> |10.10.1999|
> |11.10.1999|
> |12.10.1999|
> .....
> 
> but instead of this, i get:
> 
> +-------------------------------+
> |10.10.1999                     |
> |10.10.1999                     |
> |11.10.1999                     |
> |11.10.1999                     |
> |11.10.1999                     |
> |12.10.1999                     |
> |12.10.1999                     |
> .....
> (takes 1.02 s with -->>3555<<-- rows)
> 
> using select DATE_FORMAT(datum, '%d.%m.%Y') from Info_lnk order by
> datum
> 
> +-------------------------------+
> |10.10.1999                     |
> |10.10.1999                     |
> |11.10.1999                     |
> |11.10.1999                     |
> |11.10.1999                     |
> |12.10.1999                     |
> |12.10.1999                     |
> .....
> 
> (takes 0.61s with -->>7370 rows<<--)!!!
> 
> The same thing happens, when i use
> 
> select distinct left(datum, 8) from Info_lnk order by
> datum;
> 
> so, is this a bug or a feature?
> 
> cu
> 
> Holger

Hi Holger

This is not a bug!
Because you use an ORDER BY datum, datum is implicitely added to the SELECT result.
With DISTINCT you only eliminate those entries with identical datum.
datum is a timestamp, so it must be equal down to seconds!

What you search for is:
SELECT
	DATE_FORMAT(datum, '%d.%m.%Y') AS fdatum
FROM
	Info_lnk
GROUP BY
	fdatum

Tschau
Christian

Thread
select !distinct! does not work when using functions?Holger Ebel20 May
  • Re: select !distinct! does not work when using functions?Christian Mack20 May