I'm new to this forum and something of a novice in SQL, but I'm not sure that any of the queries presented have met the criteria requested, or at least not in the simplest manner possible. Consider the following table:
CREATE TABLE t1(f1, f2, f3); INSERT INTO "t1" VALUES('x','y','z'); INSERT INTO "t1" VALUES('a','b','c'); INSERT INTO "t1" VALUES('1','2','3'); INSERT INTO "t1" VALUES('x','b','3'); INSERT INTO "t1" VALUES('a','2','z'); INSERT INTO "t1" VALUES('1','y','c'); INSERT INTO "t1" VALUES('a','b','c'); The simplest query to find the values of f1, f2, and f3 in duplicate records, assuming a duplicate record is considered as f1=f1 AND f2=f2 AND f3=f3 would be: select f1, f2, f3 from t1 group by f1, f2, f3 having count(*) > 1 This query could probably be expanded to show the rowid of each offending record (through either a join or a union perhaps?), but at the moment I can't put that query together. Again, I'm not a pro in SQL, so if I've misunderstood or misstated anything please let me know. Jay -----Original Message----- From: Simon Davies [mailto:[EMAIL PROTECTED] Sent: Monday, November 05, 2007 13:19 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: A beginner SQL question On 05/11/2007, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Simon Davies > <simon.james.davies-gM/[EMAIL PROTECTED]> wrote: > > I use: > > > > select rowid, A, B, C > > from t > > where A||B||C in > > ( select A||B||C > > from t > > group by A, B, C > > having count(*)>1 > > ); > > Wouldn't that mistakenly consider a record ('xy', 'z', 'w') to be > duplicate of ('x', 'yz', 'w') ? > > Igor Tandetnik > oops - yes ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------