By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,693 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

Newbee Query Problem

P: 1
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
Share this Question
Share on Google+
2 Replies


P: 57
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

P: 57
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.