Re: SQL help. Counting duplicate rows (Grrrr kill users!)

From: Zev Sero <zsero_at_mail.idt.net>
Date: 1996/11/28
Message-ID: <329d230e.9243602@news.idt.net>#1/1

Try this:

select

    col1,
    count(*) / count(distinct col2)
from table
group by col1
;

Assuming, as you said, that for each value of col1, each value of col2 has been duplicated the same number of times, this should give you that number of times.

For eliminating duplicates, I've found the following useful:

begin

    for rec in (

	select
	    col1,
	    col2,
	    min(rowid) as line
	from table
	group by
	    col1,
	    col2
    ) loop
	delete from table
	where col1 = rec.col1
	  and col2 = rec.col2
	  and rowid <> rec.line
	;

    end loop;
end;

--
Zev Sero		Don't blame me, I voted for Harry Browne
zsero_at_mail.idt.net
zsero_at_technimetrics.com

Received on Thu Nov 28 1996 - 00:00:00 CST