>Hi, Is is possible to use an sql statement(s) to count and number duplicates? The original table looks something like this, showing an excerpt where there are three entries for one date:
SELECT *
FROM ch5
WHERE mdate IN (
SELECT mdate FROM ch5 ;
GROUP BY mdate ;
HAVING COUNT(mdate) > 1)
>
>Mdate Vs
>05/28/2013 v1
>05/28/2013 v1
>05/28/2013 v1
>
>
>The following is a start in that it will give me a table that shows how many duplicates exist for each date.
>
>
>SELECT mdate,COUNT(mdate) FROM ch5 ;
> INTO TABLE dups;
> GROUP BY mdate ;
> HAVING COUNT(mdate) > 1
>
>
>Here is a part of the report that the above produces:
>
>
>mdate cnt_mdate
>05/28/2013 3
>08/28/2013 2
>
>
>And by using set relationship and a do while loop, I can count and label the duplicates. But I was just wondering if an SQL statement(s) could do it all? The final output that I need is::
>
>
>Mdate cnt/date Vs
>05/28/2013 1 v1
>05/28/2013 2
>05/28/2013 3
>
>
>Thanks,
>Steve