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
ORDERby d1.url; 

select id,directory.url,title from directory, (select url from directory group by url having count(*) > 1) as list
wherelist.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@sir-mag.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.