Firstly you need a unique row identifier for each row add an identity field

SELECT * 

From yourTable

WHERE Operating_System = '2000'

AND Processor_Speed  >=500

AND Location <> 'NOT-KNOWN'

 

Use the sql above to get the rows to satisfy the first aprt of your 'IF' pseudocode and join this back onto the table (self join) on the name and delete those rows where the row identifiers are not equal.

Then run a second query for the second part of your 'IF' which again is a self join but this time you do a self to delete those with slower processor speeds.

Then write another query to remove the remaining duplicates.

I'll try to go into more detail later ..I am very busy for now.