Duplicate Data

[lp1756]

MyAppSales has duplicated a few entries. This has happened at least
two times -- in which I caught it in the act . Most recently it
happened when I installed the latest build to fix the errors
downloading sales reports. I ran the app from the debugger on my
device. Then close the debugger and ran it again directly from the
iPhone. Possibly something in the way I closed the app from the
debugger caused this problem. Anyway, the result is an artificially
higher number of sales showing in the app.

I am trying to repair the database by downloading it to my laptop and
editing it with a sqlite browser. I was able to identify two of the
known duplications but would like to write a SQL statement to identify
any other duplicates. Here's where I am stumped.

Question -- is it valid to have rows in the report table with the same
report_type_id, report_region_id, from_date and to_date? I seem to
have quite a few of those as shown by the SQL statement "SELECT
from_date, count (from_date) as numOccurences from report group by
from_date having (count(from_date) > 1)".

Can anyone describe how to identify duplicated records -- or better
yet does anyone have any sql for identifying and/or deleting them?

[lp1756]

In partial answer to my own question, here's a SQL query that I
believe will identify duplicated rows

select count(a.type_id), a.type_id, a.units, a.royalty_price,
a.report_id, a.country_code, b.from_date from sale as a, report as b
where b.report_type_id = 0 and a.report_id = b.id group by from_date,
country_code, type_id, units having count(a.type_id) >1

I have used this query and then evaluated the results row by row --
where possible comparing with iTunes Connect. I then deleted the
report and sale rows which were duplicates -- being careful to ensure
report ID consistency between the two tables.

I think this has solved the duplicate data problem but would be
interested to know if anyone has a better way to do this.

--Larry