Select - find duplicates

[Nananana]

Hi
I have some DB2 tables where the primary key is usually unique, but can
be duplicate.
I want to find the duplicates only.
For example, suppose the primary key has these values:

21
28
30
30
34
40
52
52

I want to know about 30 and 52 only.
Can I do this with a clever SQL statement?
TIA

[Tonkuma]

Re: Select - find duplicates


Nananana wrote:

Hi
I have some DB2 tables where the primary key is usually unique, but can
be duplicate.
I want to find the duplicates only.
For example, suppose the primary key has these values:
>
21
28
30
30
34
40
52
52
>
I want to know about 30 and 52 only.
Can I do this with a clever SQL statement?
TIA

Use GROUP BY and HAVING COUNT(*) 1

[Benjamin Gufler]

Re: Select - find duplicates

On 2007-01-16 10:37, Nananana wrote:

I have some DB2 tables where the primary key is usually unique, but can
be duplicate.

I don't think so. Primary keys are unique. By definition.

Benjamin

[Nananana]

Re: Select - find duplicates


Great - thanks.
I even got it to work
:-)


Tonkuma wrote:

Nananana wrote:

Hi
I have some DB2 tables where the primary key is usually unique, but can
be duplicate.
I want to find the duplicates only.
For example, suppose the primary key has these values:

21
28
30
30
34
40
52
52

I want to know about 30 and 52 only.
Can I do this with a clever SQL statement?
TIA

Use GROUP BY and HAVING COUNT(*) 1

[Phil Sherman]

Re: Select - find duplicates



Benjamin Gufler wrote:

On 2007-01-16 10:37, Nananana wrote:

>I have some DB2 tables where the primary key is usually unique, but can
>be duplicate.

>
I don't think so. Primary keys are unique. By definition.
>
Benjamin

You're confusing the usage of the term "primary key" between the
rigorous relational definition and a much older, looser, definition that
means "the most important attribute used to identify this item (not
necessarily a row).

The same term having different meanings to different individuals has
been, for over 50 years of computer use, a significant issue to
administrators, analysts, developers, and coders. (Lots of others too!)

Please don't be too harsh on someone who uses a term with a different
definition than what you expect.

Phil Sherman

[Knut Stolze]

Re: Select - find duplicates

Phil Sherman wrote:

Benjamin Gufler wrote:

>On 2007-01-16 10:37, Nananana wrote:

>>I have some DB2 tables where the primary key is usually unique, but can
>>be duplicate.

>>
>I don't think so. Primary keys are unique. By definition.
>>
>Benjamin

>
You're confusing the usage of the term "primary key" between the
rigorous relational definition and a much older, looser, definition that
means "the most important attribute used to identify this item (not
necessarily a row).
>
The same term having different meanings to different individuals has
been, for over 50 years of computer use, a significant issue to
administrators, analysts, developers, and coders. (Lots of others too!)

You are right, of course, in a general sense. But "primary key" in
relational database systems (and this is an RDBMS-group) has a well-defined
meaning...

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Nananana]

Re: Select - find duplicates


People, people.
Sorry for using the wrong term "Primary key".
I'll take the usual punishment - just tell me what it is
:-)

Thanks


Knut Stolze wrote:

Phil Sherman wrote:
>

Benjamin Gufler wrote:

On 2007-01-16 10:37, Nananana wrote:
>I have some DB2 tables where the primary key is usually unique, but can
>be duplicate.
>
I don't think so. Primary keys are unique. By definition.
>
Benjamin

You're confusing the usage of the term "primary key" between the
rigorous relational definition and a much older, looser, definition that
means "the most important attribute used to identify this item (not
necessarily a row).

The same term having different meanings to different individuals has
been, for over 50 years of computer use, a significant issue to
administrators, analysts, developers, and coders. (Lots of others too!)

>
You are right, of course, in a general sense. But "primary key" in
relational database systems (and this is an RDBMS-group) has a well-defined
meaning...
>
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Serge Rielau]

Re: Select - find duplicates

Nananana wrote:

People, people.
Sorry for using the wrong term "Primary key".
I'll take the usual punishment - just tell me what it is
:-)

Pick an arbitrary chapter in SQL2003 and translate it to plain
English.... :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference

http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

[--CELKO--]

Re: Select - find duplicates

>I have some DB2 tables where the primary key is usually unique, but can be duplicate. <<

Read what you wrote. What is the definition of a PRIMARY KEY???
Please step away from the database before you hurt yourself and other
people.

[Jonathan Leffler]

Re: Select - find duplicates

Serge Rielau wrote:

Nananana wrote:

>People, people.
>Sorry for using the wrong term "Primary key".
>I'll take the usual punishment - just tell me what it is
>:-)

Pick an arbitrary chapter in SQL2003 and translate it to plain
English.... :-)

No - that's a cruel and unusual punishment.


--
Jonathan Leffler #include <disclaimer.h >
Email: jleffler@earthl ink.net, jleffler@us.ibm .com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

[Serge Rielau]

Re: Select - find duplicates

Jonathan Leffler wrote:

Serge Rielau wrote:

>Nananana wrote:

>>People, people.
>>Sorry for using the wrong term "Primary key".
>>I'll take the usual punishment - just tell me what it is
>>:-)

>Pick an arbitrary chapter in SQL2003 and translate it to plain
>English.... :-)

No - that's a cruel and unusual punishment.

I dispense tough love. My cats can testify to that.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference

http://www.iiug.org/waiug/present/Forum2006/Forum2006.html