Subject RE: [firebird-support] Indexing on large char field
Author Donald Klopper
Nice trick I use quite often to find duplicates:

(Assuming the duplicates occur on the CRC field)

select crc, count(crc)
group by crc
order by 2 desc;

This returns the duplicate occurances of any CRC value at the top... but
also all the single occurances.

Or possibly, if you want, create a view with this, then do a "select where"
on the view ..


create view GetDups (crcValue, instances) as
select crc, count(crc) from bookings
group by crc;

then the select

select * from getdups where instances > 1


Donald Klopper

> -----Original Message-----
> From: Scott, Niall [mailto:scottn@...]
> Sent: Friday, January 23, 2004 10:57 AM
> To: ''
> Subject: [firebird-support] Indexing on large char field
> string to create an indexeable field. This works BUT I now find that there
> are duplicate CRCs which brings me to my second problem.
> I would like to use an SQL statement to find the duplicates in one field
> (Int64) the same as Access can do but I can't seem to get it write.