How to select records w/ duplicate rows.

[bbwrede]

HI,
I am having trouble with a report. I am selecting from 1 database.

Equipment Table

phone#
equipcode

I want all rows that have the same equipcode for one phone #. For example

equipment table
phone # equipcode
404-234-1232 DSL
404-234-1232 DSL
404-234-1232 XVU

I should get 404-234-1232 because that # has DSL twice. I have tried the following query:

select phone# from equipment eq1, equipment eq2
where
eq1.phone = eq2.phone and eq1.equipcode = eq2.equipcode
I of course am matching on the same record in the 2nd table. I don't have any unique field to clear out the exact same record in the other table.

Thanks for any help!
Barb

 

[lbass]

You could group on {table.phoneno} and then group on {table.equipcode}, and then go to report->edit selection formula->group and enter:

count({table.equipcode},{table.equipcode}) >= 2

This should return only those rows with duplicate codes within a particular phone number.

-LB

 

[bbwrede]

Thanks, that worked! Is there any database way to do it though? I am only asking because the table is so big the above way takes a long time to work. But it does work so I am very appreciative and not complaining!!
Barb

 

[kgreer]

If you have not found a way to do it within the query try this.

select Disintct phone#
from equipment as eq1 join equipment as eq2
on eq1.phone = eq2.phone and eq1.equipcode = eq2.equipcode

Hope this works