just make this

select count(rowthatduplicate)
from tablename
where count(rowthatduplicate) > 1
group by rowthatduplicate

On Tue, Nov 24, 2009 at 6:06 PM, Matthias Hanft <mh@...> wrote:

>
>
> Hello,
>
> Firebird 2.0.3.12981.0-r6 on Gentoo Linux. I have a table called
> AUSZUEGE and would like to create a unique index on some of the
> columns of that table. But it wouldn't let me - there are still
> some duplicate rows in that table (which I try to avoid in the
> future by creating that unique index). How can I find that dupli-
> cate rows?
>
> I try
>
> CREATE UNIQUE INDEX AUSINDEX
> on AUSZUEGE (BLZ, KONTO, AUSZUGNR, NEUGUTDATUM);
>
> (with BLZ=CHAR(8), KONTO=VARCHAR(10), AUSZUGNR=INTEGER, and
> NEUGUTDATUM=DATE)
>
> and get
> "attempt to store duplicate value (visible to active transactions)
> in unique index "AUSINDEX"."
>
> So I need to identify those duplicate values by something like
> SELECT * from AUSZUEGE where COUNT(two_consecutive_identical_rows)>1;
> but how can I say "two_consecutive_identical_rows" in SQL?
>
> I already thought of adding a temporary VARCHAR column composed
> of the string representation of the desired index fields and get
> an output ORDER BY that colum - then the two identical rows would
> already be consecutive. But how to tell FB just to show rows where
> there _are_ at least two identical consecutive rows?
>
> Thank you,
>
> Matt
>
> [Non-text portions of this message have been removed]
>
>
>


[Non-text portions of this message have been removed]