Re: SQL for getting duplicate records?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 13 Aug 1999 12:00:36 GMT
Message-ID: <37b40870.3243263@newshost.us.oracle.com>

A copy of this was sent to "Rich Gilmour" <richg_at_aaepa.com> (if that email address didn't require changing) On Thu, 12 Aug 1999 15:17:26 -0700, you 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,
>
>RIch Gilmour
>

select count(*), c1, c2, c3, c4, ....
from T
group by c1, c2, c3, c4, ....
having count(*) > 1
/

that'll show you how many times each distinct set of values ocurrs if that set of values ocurrs more then once.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Aug 13 1999 - 07:00:36 CDT