1 Reply Latest reply: Oct 24, 2012 7:25 PM by Jeff Smith Sqldev Pm-Oracle RSS

    Unique records

    943305
      I am trying to build a query using two tables. One is a table called sanctuary lakes cleaning data. The other one is called Faulire data. In cleaining table there are four years of cleaning records for each pit. I want to find the performance of these cleaned pits using failure table. Each failure date should have only one correspoding cleaning date. I wrote the following query. This query works. But it gives duplicate data. Some of the records will show two cleaning dates for a failure.

      My query is like this.

      SELECT DISTINCTROW [Sanctuary Lakes cleaning data].PIT, [Sanctuary Lakes cleaning data].[Cleaned Year], [Sanctuary Lakes cleaning data].[Cleaning Date], [Sanctuary Lakes cleaning data].[SR NO], [Failure data table].[Failure Year], Min([Failure data table].Failure_date) AS MinOfFailure_date, [Failure data table].[Failure SR no]
      FROM [Failure data table] LEFT JOIN [Sanctuary Lakes cleaning data] ON [Failure data table].Pit = [Sanctuary Lakes cleaning data].PIT
      GROUP BY [Sanctuary Lakes cleaning data].PIT, [Sanctuary Lakes cleaning data].[Cleaned Year], [Sanctuary Lakes cleaning data].[Cleaning Date], [Sanctuary Lakes cleaning data].[SR NO], [Failure data table].[Failure Year], [Failure data table].[Failure SR no]
      HAVING (((Min([Failure data table].Failure_date))>Min([Sanctuary Lakes cleaning data]![Cleaning Date])))
      ORDER BY [Sanctuary Lakes cleaning data].PIT;

      My results are as follows:
      PIT Cleaned Year Cleaning Date SR NO Failure Year Failure_date Failure SR no
      1-08 2007/08 16-Oct-07 213458 2011/12 13-Aug-11 414984
      1-08 2007/08 16-Oct-07 213458 2011/12 25-Jun-12 478589
      1-08 2009/10 19-May-10 313497 2011/12 13-Aug-11 414984
      1-08 2009/10 19-May-10 313497 2011/12 25-Jun-12 478589
      1-08 2010/11 22-Feb-11 379081 2011/12 13-Aug-11 414984
      1-08 2010/11 22-Feb-11 379081 2011/12 25-Jun-12 478589
      1-08 2011/12 17-Apr-12 458430 2011/12 25-Jun-12 478589

      I want the following results.

      PIT Cleaned Year Cleaning Date SR NO Failure Year Failure_date Failure SR no
      1-08 2010/11 22-Feb-11 379081 2011/12 13-Aug-11 414984
      1-08 2011/12 17-Apr-12 458430 2011/12 25-Jun-12 478589

      How can I modify my query?

      Please help.

      thanks

      Nish