List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:May 19 1999 3:58pm
Subject:Re: select DISTINCT blah, wibble, fnord
View as plain text  
James Tyson wrote:
> select DISTINCT blah, wibble, fnord
> Now, I was under the impression, from what I had read that distinct
> eliminated duplicates from just the column it's used on. However, one of
> my other columns is a unix timestamp (and thus is always different) and
> distinct seems to return all of them.
> Am I wrong, or is MySQL wrong?

If you're doing the above without a WHERE clause, then it should return
all DISTINCT rows of those 3 vars. If, however, you're doing something

select DISTINCT blah, wibble, fnord FROM atale 
WHERE tstamp > 19990101000000

Then tstamp becomes part of the DISTINCT. Some other SQL implementations
will give you a syntax error if you try the above. MySQL is more
forgiving, but must stay with the SQL standard of forming the rows bases
on the 4 (not 3) vars. Therefore with MySQL you could INSERT the above
into a temp table and then do another DISTINCT without the WHERE clause
to get the result you're really after. 

select DISTINCT blah, wibble, fnordJames Tyson19 May
  • Re: select DISTINCT blah, wibble, fnordVan19 May
  • Re: select DISTINCT blah, wibble, fnordJim Faucette19 May