Hello Hervé,
Thanks a lot for help. Both Martijn van Oosterhout and yours do job god. Execution time is now about 1 to 2 seconds. I also figured myself what i was doing wrong with join and error i got. If enybody need this here is both three solutions. select distinct d1.id,d1.url,d1.title from directory d1 inner join directory d2 on d2.url = d1.url where d1.id <> d2.id ORDER by d1.url; select id,directory.url,title from directory, (select url from directory group by url having count(*) > 1) as list where list.url = directory.url; select d1.id, d1.url, d1.tittle from directory d1 where exists (select url from directory d2 where d1.url=d2.url and d2.oid>d1.oid) order by d1.url; -- Best regards, Uros mailto:[EMAIL PROTECTED] Tuesday, June 24, 2003, 1:27:44 PM, you wrote: HP> Hi, HP> I think this could be good : HP> select d1.id, d1.url, d1.tittle HP> from directory d1 HP> where exists (select url from directory d2 where d1.url=d2.url and d2.oid>>d1.oid) HP> order by d1.url; HP> Hope this will help, HP> Regards, HP> Le Mardi 24 Juin 2003 12:16, Uros a écrit : >> 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. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
