Re: [sqlite] Re: A beginner SQL question
Igor Tandetnik > 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 Due the fact that, in this case, the columns are integers, can be done some trickery in the concatenation to avoid that? Some like: A||'*'||B||'*||C ? A.J.Millan - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: A beginner SQL question
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 > 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] -
Re: [sqlite] Re: A beginner SQL question
On 05/11/2007, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Simon Davies > 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] -
RE: [sqlite] Re: A beginner SQL question
Igor: The proposed solution seem list all rows in the table. Perhaps miss some thing in between myTable t1 and myTable t2? Gerry: Yours solution does not list any at all. It is likely that there are not duplicated entrys (at least must not happen that.). Thanks for yours input. A.J.Millan A.J.Millan <> wrote: Suppose a table: CREATE TABLE 'myTable' ( A INTEGER NOT NULL, B INTEGER NOT NULL, C INTEGER); Do is there some query to return if there are some duplicate files and/or who are they? Consider duplicate file if there are two or more rows with the same values. I.E: two or more with same values A=X, B=Y, C=Z or A= X, B=Y, C=NULL for any 3-tuple X Y Z select t1.A, t1.B, t1.C from myTable t1, myTable t2 where t1.A = t2.A and t1.B = t2.B and (t1.C = t2.C or (t1.C is null and t2.C is null)); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: A beginner SQL question
Igor Tandetnik wrote: A.J.Millan <[EMAIL PROTECTED]> wrote: Suppose a table: CREATE TABLE 'myTable' ( A INTEGER NOT NULL, B INTEGER NOT NULL, C INTEGER); Do is there some query to return if there are some duplicate files and/or who are they? Consider duplicate file if there are two or more rows with the same values. I.E: two or more with same values A=X, B=Y, C=Z or A= X, B=Y, C=NULL for any 3-tuple X Y Z select t1.A, t1.B, t1.C from myTable t1, myTable t2 where t1.A = t2.A and t1.B = t2.B and (t1.C = t2.C or (t1.C is null and t2.C is null)); I think that something like: select A, B, C from myTable except select distinct A, B, C from myTable; should work, too. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -