Dinstinct

[NeoPuma]

I wish to get all records from a database - including all fields, but I don't want any which has the same 'simile_url' as any other record.

For example:
ID Title URL
1 Happy happy.gif
2 Smile happy.gif
3 Laughing laughing.gif
4 Crying crying.gif
5 Upset crying.gif

Records 1, 3 and 4 would only be returned because the others have 'double' urls.

Any ideas Please? :D

[guelphdad]

since record 1 and 2 have the same URL and you only want to return the one of them which one do you choose? smallest id where there is a case of duplicate URL?

you also say you want all records but that will not be possible since you then say you want to discard some of the rows.

[Beagle]

check out the SELECT syntax documentation at mysql.org and checkout the DISTINCT keyword

[guelphdad]

Beagle, distinct won't help in this case. It would return all rows since they are all distinct from one another. the problem is in deciding which of the duplicate URL rows the user wants returned. if it is as simple as the lowest id in the case of duplicate URLs then that is the query I will show.

[NeoPuma]

I want all rows UNLESS they have a url the same as another record. In this case, the first one returned with that url is the one that is chosen.

That a bit better?

[Fumigator]

If you only want to retrieve that one field (URL), then you can use distinct.

SELECT DISTINCT(field) FROM table;


If you need to retrieve the entire row, use GROUP BY.

SELECT DISTINCT * FROM table GROUP BY field;

[guelphdad]

don't use DISTINCT and GROUP BY they are mutually exclusive.

use something like this:

select
min(id),
min(title),
url
from yourtable
group by url