Hi

I want to retreive duplicates

table

report_id   sched_id    ID13                   ID14                ID15      

61      1458    COUNTRYWIDE    SUPER EAGLE   NO
61      1478    CAPITOL                   SUPER EAGLE   NO
61      1450    COUNTRYWIDE    SUPER EAGLE   NO

now sched_id 1458 and 1450 is duplicate

my query is

select id12,id13,id14,id15,report_id,count(sched_id) as cnt
from #tmp group by id12,id13,id14,id15,report_id

i want my output to be

report_id   sched_id    ID13                   ID14                ID15
61      1458    COUNTRYWIDE    SUPER EAGLE   NO
61      1450    COUNTRYWIDE    SUPER EAGLE   NO

please help me to solve this.

thank you

Hello, vanitha

Assuming your columns don't allow NULL-s, try something like this:

SELECT * FROM YourTable a
WHERE EXISTS (
  SELECT * FROM YourTable b
  WHERE a.report_id=b.report_id
  AND a.ID13=b.ID13 AND a.ID14=b.ID14 AND a.ID15=b.ID15
  AND a.sched_id<>b.sched_id
)

Razvan