Article View: borland.public.interbase.sql
Article #655Re: retrieve rows where one field is duplicated
From: RB Smissaert
Date: Wed, 24 Oct 2007 13:11
Date: Wed, 24 Oct 2007 13:11
41 lines
2101 bytes
2101 bytes
Nice one, that does exactly the job. Thanks. RBS "Karol Bieniaszewski" <liviuslivius@poczta.onet.pl> wrote in message news:471ef377@newsgroups.borland.com... > > 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:
<471f44c0@newsgroups.borland.com>Path:
arc1-ca.pugleaf.net!g2news2.google.com!news1.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mailReferences:
<471e654e@newsgroups.borland.com> <471ef377@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: "RB Smissaert" <bartsmissaert@blueyonder.co.uk> Newsgroups: borland.public.interbase.sql References: <471e654e@newsgroups.borland.com> <471ef377@newsgroups.borland.com> Subject: Re: retrieve rows where one field is duplicated Date: Wed, 24 Oct 2007 14:11:59 +0100 Lines: 42 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit 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 NNTP-Posting-Host: 82.37.28.137 Message-ID: <471f44c0@newsgroups.borland.com> X-Trace: newsgroups.borland.com 1193231552 82.37.28.137 (24 Oct 2007 06:12:32 -0700) Path: arc1-ca.pugleaf.net!g2news2.google.com!news1.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail