title: Way to find Distinct values from a table by a specific column
body: <p>Here is a table consisting of data like below:</p>
  
  <pre><code>StandardName|Username    |RType
  ------------|------------|--------
  Department  |Department  |  Position
  Division    |Division    |  Entity
  Division    |Division    |  Position
  Plant       |Plant       |  Entity
  Section     |Section     |  Position
  SubDivision |Sub-Division|  Entity
  SubDivision |Subdivision |  Position
  SubSection  |Subsection  |  Position
  Unit        |Unit        |  Entity
  </code></pre>
  
  <p>I want all the StandardName and UserName followed by Rtype = 'Entity' and In case of Rtype = 'Position' it will only get the StandardName those are not associated with RType  'Entity'</p>
  
  <p>For doing this I did the query like this</p>
  
  <pre><code>SELECT DISTINCT u.StandardName, u.UserName ,ISNULL(e.RType,'position') AS RType
  from (
       SELECT  DISTINCT StandardName,UserName
       from Table1 AS ee where   RType = 'Entity'  
       UNION
       SELECT  DISTINCT StandardName, UserName
       from Table1 AS pp where  RType = 'position' 
  ) u
  LEFT OUTER JOIN (
       select UserName,StandardName,RType 
       from Table1 WHERE RType='Entity' 
  ) e on e.StandardName = u.StandardName
   ORDER BY Rtype
  </code></pre>
  
  <p>Then the output is </p>
  
  <pre><code>StandardName|Username    |RType
  ------------|------------|--------
  Division    |Division    |  Entity
  Plant       |Plant       |  Entity      
  SubDivision |Sub-Division|  Entity
  SubDivision |Subdivision |  Entity       
  Unit        |Unit        |  Entity
  Department  |Department  |  Position
  Section     |Section     |  Position
  SubSection  |Subsection  |  Position
  </code></pre>
  
  <p>Here in Case of 'SubDivision' it appears two times, whereas it needs to appear one time with the 'Entity' RType.  </p>
  
  <p>The Expected output should be--    </p>
  
  <pre><code>StandardName|Username    |RType
  ------------|------------|--------
  Division    |Division    |  Entity
  Plant       |Plant       |  Entity      
  SubDivision |Sub-Division|  Entity            
  Unit        |Unit        |  Entity
  Department  |Department  |  Position
  Section     |Section     |  Position
  SubSection  |Subsection  |  Position
  </code></pre>
  
  <p>The SQLFIDDLE <a href="http://sqlfiddle.com/#!18/3fe28/17/0" rel="nofollow noreferrer">Link</a></p>