This discussion is archived
2 Replies Latest reply: Apr 8, 2013 6:40 PM by 508336 RSS

Can this be solved by a nested query?

508336 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points