I have a table called 'Coordinate' which contains several unique Coordinate_IDs. Every Coordinate_ID is assigned a Location_ID (which is drawn from another table). The problem that I'm facing is that within this coordinate table, there are several duplicated Location_IDs and so one particular Location_ID may be associated with two or more different Coordinate_IDs (ie Coordinate_ID = 123 has a Location_ID = 555, and Coordinate_ID = 879 also has a Location_ID = 555). I am supposed to remove all records with the more outdated Effective_From_Date.
So to go off of the above example, if Coordinate_ID = 123 has an Effective_From_Date of 29-OCT-07 and Coordinate_id = 879 has a Effective_From_Date of 16-JAN-12 I want to select Coordinate_ID = 123 with my query so that I can remove it.
I'm very new to Oracle and was wondering if anyone could help me out with this query.
If it helps at all, this is the query I ran to gather the duplicated Location_IDs
select count(location_id), location_id from coordinate
where current_indicator_lf = 'Y'
having count(location_id) > 1
group by location_id
order by location_id;