How to delete records that match a condition and leave one?
<p>I have a stored procedure where I want to delete all records but one that match a certain condition. I can't seem to figure it out.</p>

<p>I have the following temporary table:</p>

<pre><code>CREATE TABLE #TEMP_RESULTS(
    code NVARCHAR(50),
    newCode NVARCHAR(50),
    result NVARCHAR(10)
)
</code></pre>

<p>Then I have code that inserts data in that table. Basically, the records look like that:</p>

<pre><code>code  newCode   result
A1      B1       &lt;15
A1      B2       &lt;20
A1      B3       &lt;30 -- in this case one of the three A1s should stay in the table
A2      B4        25 -- this is staying
A2      B5       &lt;45
A3      B6       &lt;22
</code></pre>

<p>After that, I am trying to:</p>

<pre><code>DELETE TR
FROM #TEMP_RESULTS TR
WHERE   TR.result LIKE '&lt;%' -- here I need to add another condition that can leave one row in the table
</code></pre>

<p><strong>EDIT</strong>: Forgot to mention, that searched on the Internet, but most of the topics were about removing duplicates or selecting TOP records.</p>

<p><strong>EDIT2</strong>: This is the desired output:</p>

<pre><code>code  newCode   result
A1      B1       &lt;15
A2      B4        25 -- this is staying
A2      B5       &lt;45
A3      B6       &lt;22
</code></pre>

<p>This is done, because later I would like to update the result to something else. There is no constraint and no grouping.</p>