Re: [sqlite] Re: A beginner SQL question

[A.J.Millan]

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

[Rosenblum, Jason]

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

[Simon Davies]

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

[A.J.Millan]

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

[Gerry Snyder]

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