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 <[EMAIL PROTECTED]> 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
pgp00000.pgp
Description: PGP signature
