2 Replies Latest reply: Apr 8, 2013 8:40 PM by 508336 RSS

    Can this be solved by a nested query?

    508336
      Hello,

      Can anybody help me? Thanks a lot.

      We have a test system that test production units, and output data to the TestData table in the database. The TestData table schema is like the follows. For each unit, it may pass the test system for multiple times, and each time the system generate a row of data in the table. 'Not Found' means the unit passed that time of test, no defect is found. If a defect is found the defect will be logged in the table.

      The question is: I want to form a new table named TestRslt based on the TestData. The TestRslt table should be like follows, ie. each unit should have only one row of test result. For units tested with both Defect-A and Defect-B, only the row containing Defect-B will be appear in the result table. For a unit of which Defect-B is found in multiple tests, only the first time test data will be appear in the result table.

      TestData
      UnitID TestNumber Defect
      unit-A 1 Not Found
      unit-B 1 Defect-A
      unit-B 2 Defect-B
      unit-B 3 Not Found
      unit-B 4 Defect-A
      unit-C 1 Defect-B
      unit-C 2 Defect-B
      unit-C 3 Defect-A



      TestRslt
      UnitID TestNumber Defect
      unit-A 1 Not Found
      unit-B 2 Defect-B
      unit-C 1 Defect-B