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]

Use GROUP BY and HAVING COUNT(*) > 1

[Benjamin Gufler]

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]

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

[Phil Sherman]

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]

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]

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

Thanks

[Serge Rielau]

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

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

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: jlef...@earthlink.net, jlef...@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

[Serge Rielau]

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.