Re: retrieve rows where one field is duplicated

Article View: borland.public.interbase.sql

Article #654

Back to borland.public.interbase.sql Previous Next

#654

From: Karol Bieniaszewski
Date: Wed, 24 Oct 2007 07:31

31 lines
1795 bytes

Uzytkownik "RB Smissaert" <bartsmissaert@blueyonder.co.uk> napisal w 
wiadomosci news:471e654e@newsgroups.borland.com...
> What would be the quickest way to retrieve all the rows
> from a table where one field has duplicates in another row? So for example 
> if we want the rows where f1 has
> duplicates and the following table:
>
> f1    f2
> --------
> A    1
> A    2
> B    3
> C    4
> C    5
>
> retrieve all the rows except the one with B and 3.
> I can do it with a subquery and group by and count > 1
> but it is very slow.
> I am coming to think that this may be best done in code
> rather than in SQL, but maybe knows a way.
>
> RBS


try this

SELECT * FROM TABLEXXX X1
WHERE (EXISTS(SELECT * FROM TABLEXXX X2 WHERE X2.ID<>X1.ID AND X2.F1=X1.F1))

Karol Bieniaszewski

Technical Details

Message-ID: <471ef377@newsgroups.borland.com>

Path:

arc1-ca.pugleaf.net!g2news2.google.com!news4.google.com!proxad.net!feeder1-2.proxad.net!213.200.89.82.MISMATCH!tiscali!newsfeed1.ip.tiscali.net!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

References: <471e654e@newsgroups.borland.com>

Show Full Headers

Complete Article Headers

X-Google-Thread: 114a0e,2519d605a4e0cfb9
X-Google-Attributes: gid114a0e,public,usenet
X-Google-Language: ENGLISH,ASCII-7-bit
From: "Karol Bieniaszewski" <liviuslivius@poczta.onet.pl>
Newsgroups: borland.public.interbase.sql
References: <471e654e@newsgroups.borland.com>
Subject: Re: retrieve rows where one field is duplicated
Date: Wed, 24 Oct 2007 09:31:17 +0200
Lines: 33
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 195.205.151.58
Message-ID: <471ef377@newsgroups.borland.com>
X-Trace: newsgroups.borland.com 1193210743 195.205.151.58 (24 Oct 2007 00:25:43 -0700)
Path: arc1-ca.pugleaf.net!g2news2.google.com!news4.google.com!proxad.net!feeder1-2.proxad.net!213.200.89.82.MISMATCH!tiscali!newsfeed1.ip.tiscali.net!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

Reply to Parent Message