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]
-----------------------------------------------------------------------------
