duplicate record

[ocpdude]

I am trying to clean up this table (bill_info).

fpn job_no description
--- -------- --------------
X01 Z001 DUDE
X01 Z001 DUDE/
X02 Z002 HI
X02 Z002 HI,
X03 Z003 WORLD
X04 Z004 GOD


How can I show all the uplicate record so I can do the
clean up. I've tried to use this

select fpn, job_no, description
from bill_info
group by fpn, job_no,description
having count(*) > 1;
but it only returns one record which are duplicate ones.
I need to see all the records wich are duplicate so I can choose which one I want to delete

Thanks,

[marist89]

Assuming that fpn,job_no is unique:
select * from bill_info
where (fpn,job_no) in (select fpn, job_no from bill_info group by fpn,job_no having count(*) > 1)

or you can use:
select t1.* from bill_info t1, bill_info t2
where t1.fpn = t2.fpn
and t1.job_no = t2.job_no
and t1.description <> t2.description

[ocpdude]

Thanks a million,
the first one worked fine but the second gave me wrong info.

Anyway, HOW CAN I DELETE THOSE RECORDS. (of course
I have to keep one of each :) ) ??????

thanks,