Newbee Query Problem

horsecock

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

  select field_3 from CUSTOMER where field_3 <> ' ' group by field_3 having count(*) > 1 
 

What doesn't work,

Expand|Select|Wrap|Line Numbers

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

cburnett

Provided you are on V8.1 or later, try the following:

Expand|Select|Wrap|Line Numbers

 create table test(col1 char(3),

                  col2 char(3),

                  col3 char(3));
 
insert into test values ('1A','1B','1C');

insert into test values ('2A','2B','2C');

insert into test values ('3A','3B','3C');

insert into test values ('3A','3B','3C');

insert into test values ('3A','3B','3C');

insert into test values ('4A','4B','4C');

insert into test values ('5A','5B','5C');

insert into test values ('5A','5B','5C');
 
with x(rnum, col1, col2, col3) as (select rownumber() over(order by col1, col2, col3),

                                          col1,

                                          col2,

                                          col3

                                     from test)

select distinct x1.col1,

                x1.col2,

                x1.col3

  from x x1,

       x x2

 where x1.col1 = x2.col1

   and x1.col2 = x2.col2

   and x1.col3 = x2.col3

   and x1.rnum <> x2.rnum

cburnett

Thinking some more on this, there is a much simpler way:

Expand|Select|Wrap|Line Numbers

 select distinct col1,

                col2,

                col3

  from test

 group by col1,

          col2,

          col3

 having count(*) > 1

Newbee Query Problem

Post your reply

Similar topics