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>
klint.
+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : [EMAIL PROTECTED] : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+
