Re: Selecting dupes from table

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:uros(at)sir-mag(dot)com

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.