0 Replies Latest reply: Aug 22, 2012 1:10 PM by 957310 RSS

    Filter out based on most recent "Effective_From_Date

      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;