Hi,
Can someone please help me with this duplicate query.
I'm trying to:
1. Return duplicates only. (without including the first valid record), and
2. Return as duplicate if the difference between a.inspection_time
and b.inspection time is under 5 minutes.
Here's the query string I'm using to retrieve the duplicates but it is
returning every duplicate records.
select a.rego,
a.inspection_date,
a.inspection_time,
count(*) as c
from inspections a
where
exists (
select null
from inspections b
where
a.rego = b.rego
and a.inspection_date = b.inspection_date
and a.inspection_time = b.inspection_time
group by
b.rego, b.inspection_date, b.inspection_time
)
and status_id in (0)
group by
a.rego, a.inspection_date, a.inspection_time
having count(*) > 1
Thanks in advance.