Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jazzclassics
Starting Member
3 Posts |
Posted - 2013-04-07 : 04:47:43
|
Hello,I have been writting SQL for two years and cannot see a solution to the following problem. 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.TestDataUnitID TestNumber Defectunit-A 1 Not Foundunit-B 1 Defect-Aunit-B 2 Defect-Bunit-B 3 Not Foundunit-B 4 Defect-Aunit-C 1 Defect-Bunit-C 2 Defect-Bunit-C 3 Defect-ATestRsltUnitID TestNumber Defectunit-A 1 Not Foundunit-B 2 Defect-Bunit-C 1 Defect-B |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-07 : 21:07:49
|
Query like this:select * from (select *, row_Number() over ( partition by unitid order by case when Defect = 'Not Found' then 1 else 0 end, Defect desc ) as RNfrom YourTable ) s where RN = 1; |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-08 : 02:03:05
|
[code]DECLARE @TestData TABLE(UnitID VARCHAR(10), TestNumber INT, Defect VARCHAR(15))INSERT INTO @TestDataSELECT 'unit-A', 1, 'Not Found' union allSELECT 'unit-A', 2, 'Defect-A' union allSELECT 'unit-B', 1 , 'Defect-A' union allSELECT 'unit-B', 2 , 'Defect-B' union allSELECT 'unit-B', 3 , 'Not Found' union allSELECT 'unit-B', 4 , 'Defect-A' union allSELECT 'unit-C', 1 , 'Defect-B' union allSELECT 'unit-C', 2 , 'Defect-B' union allSELECT 'unit-C', 3 , 'Defect-A'/*TestRsltUnitID TestNumber Defectunit-A 1 Not Foundunit-B 2 Defect-Bunit-C 1 Defect-B*/SELECT UnitID, TestNumber, DefectFROM (SELECT *, ROW_NUMBER() OVER( PARTITION BY UnitID ORDER BY CASE WHEN Defect = 'Defect-B' THEN 1 ELSE 0 END DESC ) rn FROM @TestData WHERE Defect IN ( 'Defect-B', 'Not Found') ) tWHERE t.rn = 1;[/code] |
|
|
jazzclassics
Starting Member
3 Posts |
Posted - 2013-04-08 : 22:19:53
|
quote: Originally posted by James K Query like this:select * from (select *, row_Number() over ( partition by unitid order by case when Defect = 'Not Found' then 1 else 0 end, Defect desc ) as RNfrom YourTable ) s where RN = 1;
Thank you very much James. I will try your method and report back after. My data model is much more complecated than the above one. So it takes some time. Thank you |
|
|
jazzclassics
Starting Member
3 Posts |
Posted - 2013-04-08 : 22:21:17
|
quote: Originally posted by bandi
DECLARE @TestData TABLE(UnitID VARCHAR(10), TestNumber INT, Defect VARCHAR(15))INSERT INTO @TestDataSELECT 'unit-A', 1, 'Not Found' union allSELECT 'unit-A', 2, 'Defect-A' union allSELECT 'unit-B', 1 , 'Defect-A' union allSELECT 'unit-B', 2 , 'Defect-B' union allSELECT 'unit-B', 3 , 'Not Found' union allSELECT 'unit-B', 4 , 'Defect-A' union allSELECT 'unit-C', 1 , 'Defect-B' union allSELECT 'unit-C', 2 , 'Defect-B' union allSELECT 'unit-C', 3 , 'Defect-A'/*TestRsltUnitID TestNumber Defectunit-A 1 Not Foundunit-B 2 Defect-Bunit-C 1 Defect-B*/SELECT UnitID, TestNumber, DefectFROM (SELECT *, ROW_NUMBER() OVER( PARTITION BY UnitID ORDER BY CASE WHEN Defect = 'Defect-B' THEN 1 ELSE 0 END DESC ) rn FROM @TestData WHERE Defect IN ( 'Defect-B', 'Not Found') ) tWHERE t.rn = 1;
Bandi, Thank you for your detailed answer to my question. I will try your method and report back after. My data model is much more complecated than the above one. So it takes some time. Thank you |
|
|
|
|
|
|
|