I have 2 MySQL tables from which 2 tables I need to get the following result set:
SELECT url, LEFT(title, 25) AS mini_title, LEFT(description, 45) AS mini_desc, traffic_xchange.id
FROM confirm_comm_link, traffic_xchange
WHERE confirm_comm_link.id = url_id
AND clicks_del <= clicks_rec AND title is not null
order by RAND() LIMIT 4
But the above SQL command can produce duplicate url values.
I need the rows returned have unique (aka distinct) url values.
How does one do that?
FYI, I thought putting distinct around url would do that, but it is not doing it.
That is I tried this:
SELECT distinct(url), LEFT(title, 25) AS mini_title, LEFT(description, 45) AS mini_desc, traffic_xchange.id
FROM confirm_comm_link, traffic_xchange
WHERE confirm_comm_link.id = url_id
AND clicks_del <= clicks_rec AND title is not null
order by RAND() LIMIT 4
but the above is still produces duplicate url values.
WOW!
That is a real eye opener.
I did not know that.
In fact this is such a strange way, exceptional way, for MySQL to operate.
I mean when we issue this command SELECT COUNT(id)… FROM
then the COUNT only operates on the id and not on everything else after it.
when people write SELECT DISTINCT(foo), other, columns FROM … it almost always means that they think DISTINCT operates only on foo, when in fact the parentheses are not necessary because DISTINCT operates on all columns in the SELECT clause
When using GROUP BY url in the right side of the MySQL command, does one need to still use DISTINCT(url) on the left side still?
It seems like it is not necessary, but just wondering!
ThanX.
I seem to remember reading an article on optimization at MySQL.com that suggested using GROUP BY in favor of DISTINCT whenever possible. I’m having trouble finding it at the moment though.
