How do retrieve unique rows where all all columns are the same except id So it looks like for some entries to the table the data is being written multiple times from our system, so all the rows entered for a specific data item are written 3 times, with exactly the same data apart from the auto-generated ID is different (incremented by 1 each time).

How would I retrieve all the essentially unique rows from the table? A join on itself doesn't seem to be working as the IDs are different.
Omit the ID column from your SELECT ... statement and and a DISTINCT. As in: 

SELECT DISTINCT COLUMN2, COLUMN3, .... FROM TABLE1


If you want to find how many rows exist for the same values then do a COUNT() combined with a GROUP BY. As in:

SELECT COUNT(ID), COLUMN2, COLUMN3, COLUMN4, .... 
    FROM TABLE1 
    GROUP BY COLUMN2, COLUMN3, COLUMN4, ....


Reference Material (MySQL Dev Site)


8.2.1.18 DISTINCT Optimization
3.3.4.8 Counting Rows  
12.20.2 GROUP BY Modifiers