Finding duplicates only.

[Greenhorn <user.postgresql@xxxxxxxxx>]

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.

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general