Keep all unique data in one column in SQL
<p>I have a query to check the duplicates from a table:-</p>

<pre><code>(SELECT assignment_name 
 FROM   (SELECT xx.supervisor_stg.*, 
                Row_number() 
                  over ( 
                    PARTITION BY assignment_name 
                    ORDER BY assignment_name) AS c 
         FROM   xx.supervisor_stg) 
 WHERE  c &gt; 1) 
</code></pre>

<p>I want to delete the duplicate data from these data but such that if one value like assignment_name 'Smith' is written thrice then 1 value should remain and the other 2 should be deleted.</p>

<p>When i am using the following query i am getting an error :</p>

<pre><code>Delete From supervisor_stg
where asg_name &lt;&gt;(SELECT Asg_Name 
 FROM   (SELECT xx.supervisor_stg.*, 
                Row_number() 
                  over ( 
                    PARTITION BY assignment_name 
                    ORDER BY assignment_name) AS c 
         FROM   xx.supervisor_stg) 
 WHERE  c &gt; 1) 


SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:
</code></pre>