Urgent

[vanitha]

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

[Razvan Socol]

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