470,612 Members | 2,506 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,612 developers. It's quick & easy.

Select - find duplicates

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

Jan 16 '07 #1
10 7668

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

Jan 16 '07 #2
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
Jan 16 '07 #3

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
Jan 16 '07 #4


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
Jan 16 '07 #5
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
Jan 16 '07 #6

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
Jan 17 '07 #7
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/Fo...Forum2006.html
Jan 17 '07 #8
>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.

Jan 17 '07 #9
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: jl******@earthlink.net, jl******@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
Jan 18 '07 #10
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/Fo...Forum2006.html
Jan 18 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by bellefy | last post: by
4 posts views Thread by Justin Koivisto | last post: by
3 posts views Thread by Kall, Bruce A. | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.