Re: Selecting dupes from table

On Tue, Jun 24, 2003 at 12:16:43PM +0200, Uros wrote:
> Hello ,
>
> I have table directory with 3 columns (id,url,title)
>
> I want to list all entries with duplicate urls.
>
> I tried this:
>
> select id,url,title from directory where url IN
> (select url from directory group by url having count(url) > 1)
> ORDER by url;
>
> but this takes 30 seconds with 25.000 entries. I have index on url.
>
> Can I use any other query to select this faster.

How about:

Duplicate urls would be given by:

select url from directory group by url having count(*) > 1;

To get all the entries with those urls, something like:

select id,url,title from directory,
(select url from directory group by url having count(*) > 1) as list
where list.url = directory.url;

I hope I got the syntax right.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington