R: SQL for getting duplicate records?

From: Andrea LUCIANO <andrea.luciano_at_icteam.it>
Date: Tue, 17 Aug 1999 11:20:06 +0200
Message-ID: <o79bp7.pc4.ln@turner.icteam.it>

Try also:

SELECT CheckDup.keyValue ,

        CheckDup.viewingValue1 ,
        CheckDup.viewingValue2 ,
        CheckDup.viewingValueNthNumber

FROM duptable CheckDup
WHERE exists (SELECT 1

                         FROM duptable Tmp
                         WHERE CheckDup.keyValue = Tmp.keyValue)

ORDER BY CheckDup.keyValue;

AL

Boyce G. Williams, Jr. <bgwillia_at_vcu.edu> wrote in message 37b8337f.12772350_at_usenet.acw.vcu.edu...
> On Thu, 12 Aug 1999 15:17:26 -0700, "Rich Gilmour" <richg_at_aaepa.com>
> wrote:
>
> >
> >Hi all.
> >
> >Forgive me if this sounds really stupid, but I'm new to SQL. Could
somebody
> >please explain how to properly contsruct a SQL statement that returns all
> >records in a table with matching field values? I've managed to pull
distinct
> >values, but thats not what I'm after.
> >
> >Thanks in advance,
>
> You might try this:
>
> SELECT CheckDup.keyValue ,
> CheckDup.viewingValue1 ,
> CheckDup.viewingValue2 ,
> CheckDup.viewingValueNthNumber
> FROM duptable As CheckDup
> WHERE (((CheckDup.keyValue) In (SELECT Tmp.keyValue
> FROM duptable As Tmp
> GROUP BY Tmp.keyValue
> HAVING Count(*)>1 )))
> ORDER BY CheckDup.keyValue;
>
> Where keyValue is the value(s) you want to check for duplicates, and
> viewingValue# is one or more additional values you want to view as
> well.
>
> I pulled this code from a MS-Access check duplicate value query and
> modified it for Oracle usage as best I can. It should work with some
> modifications to get your values.
>
> Good luck,
> Boyce G. Williams, Jr.
Received on Tue Aug 17 1999 - 04:20:06 CDT