On Thu, 2005-01-27 at 14:40 +1100, Klint Gore wrote:
> On Wed, 26 Jan 2005 21:25:19 -0500, "D. Richard Hipp" <[EMAIL PROTECTED]>
> wrote:
> > On Wed, 2005-01-26 at 17:45 -0800, Clark Christensen wrote:
> > > I'm new to SQL, and SQLite, and I find myself needing to
> > > identify duplicate records in a SQLite table (there are
> > > about 2K duplicates in a 36K row table). Any suggestions
> > > or magic SQL queries appreciated :-)
> > >
> >
> > SELECT * FROM table EXCEPT SELECT DISTINCT * FROM table;
>
> Does that actually work?
>
> sqlite> create table firstnames (fname varchar(5));
> sqlite> insert into firstnames values ('bob');
> sqlite> insert into firstnames values ('bob');
> sqlite> insert into firstnames values ('bill');
> sqlite> select * from firstnames except select distinct * from firstnames;
> sqlite>
> sqlite> select fname from firstnames group by fname having count(*) > 1;
> bob
> sqlite>
>
You are right - my original suggestion does not work. I forgot
that the EXCEPT operator does a DISTINCT on the result set of
both operands. Your approach works much better.
--
D. Richard Hipp <[EMAIL PROTECTED]>
