There is a table as follows, would like to go to the weight according to the serial number, but first records of the results of the reserved for "success", how do you write SQL statement?

ID number results
Failure of the 54610 392478201163006
19516 122171001162247 unknown
88 071950201199833 unknown
113357, 090246701106864 successful
2594 121268601151695 unknown
Failure of the 56135 444719001113492
52843 333778701109253 unknown
135595, 290091001142190 successful
Failure of the 21483 183013101120417
Failure of the 20651 115199801157643
126540, 243079201146037 successful
Failure of the 19516 122171001162247
92748 505599001114760 unknown
Failure of the 37989 283371201181498
Failure of the 20723 115199801157643
Failure of the 52843 333778701109253
18420 062140101106161 unknown
Failure of the 24071 264434301197793
56135 444719001113492 unknown
4262 220403701154822 unknown
Failure of the 92748 505599001114760
26538 344555901166962 unknown
54610 392478201163006 unknown
2679 185197001121513 unknown
104366, 311500001180172 successful
136568, 350577001176525 successful
24071 264434301197793 unknown
107647 495409301144376 unknown
107647, 495409301144376 successful
21483 183013101120417 unknown
20723 115199801157643 unknown
Failure of the 26538 344555901166962
20651 115199801157643 unknown
Failure of the 45298 055662501165612
Failure of the 18515 062140101106161
101829 264125601140475 unknown
101829, 264125601140475 successful
104366 311500001180172 unknown

CodePudding user response:

Row_number () over (partition by serial number order by charindex (as a result, the 'success, failure, unknown)) as rids

Where rids=1

CodePudding user response:

 
Serial number, SELECT, Anderson D a. a. results FROM (
The SELECT ROW_NUMBER () OVER (PARTITION BY serial number ORDER BY results) num, ID, serial number, the result FROM the data table name 
) WHERE a a.n um=1