467,887 Members | 1,550 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Newbee Query Problem

Hi,

Being new to DB2 I'm having a small problem that I'm sure is quite easy to someone smarter than me.

I have a table with duplicate entries and I need to know how to code a query to exctract certain fields from the table with a count > 1. I can do this with one field however I need to do this with multiple fields.

Working 1 Field Query,
Expand|Select|Wrap|Line Numbers
  1.  select field_3 from CUSTOMER where field_3 <> ' ' group by field_3 having count(*) > 1 
What doesn't work,
Expand|Select|Wrap|Line Numbers
  1.  select * from CUSTOMER where field_3 <> ' ' group by field_3 having count(*) > 1 
Table Name: CUSTOMER
# of Fields: 5 (e.g. Filed_1, Field_2, Field_3, etc)

If some could help me with this I would be eternally grateful.

Thanks in advance.......
Jul 23 '07 #1
  • viewed: 1096
Share:
2 Replies
Provided you are on V8.1 or later, try the following:

Expand|Select|Wrap|Line Numbers
  1. create table test(col1 char(3),
  2.                   col2 char(3),
  3.                   col3 char(3));
  4.  
  5. insert into test values ('1A','1B','1C');
  6. insert into test values ('2A','2B','2C');
  7. insert into test values ('3A','3B','3C');
  8. insert into test values ('3A','3B','3C');
  9. insert into test values ('3A','3B','3C');
  10. insert into test values ('4A','4B','4C');
  11. insert into test values ('5A','5B','5C');
  12. insert into test values ('5A','5B','5C');
  13.  
  14. with x(rnum, col1, col2, col3) as (select rownumber() over(order by col1, col2, col3),
  15.                                           col1,
  16.                                           col2,
  17.                                           col3
  18.                                      from test)
  19. select distinct x1.col1,
  20.                 x1.col2,
  21.                 x1.col3
  22.   from x x1,
  23.        x x2
  24.  where x1.col1 = x2.col1
  25.    and x1.col2 = x2.col2
  26.    and x1.col3 = x2.col3
  27.    and x1.rnum <> x2.rnum
Aug 2 '07 #2
Thinking some more on this, there is a much simpler way:

Expand|Select|Wrap|Line Numbers
  1. select distinct col1,
  2.                 col2,
  3.                 col3
  4.   from test
  5.  group by col1,
  6.           col2,
  7.           col3
  8.  having count(*) > 1
Aug 3 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Newbee | last post: by
2 posts views Thread by Newbee Adam | last post: by
1 post views Thread by Magnus | last post: by
2 posts views Thread by Martin Hvidberg | last post: by
6 posts views Thread by tony | last post: by
2 posts views Thread by Rene | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.