title: Distinct Union on Select. Errors on ntext data type
body: <p>I'm pretty useless at SQL but I find myself having to write a stored procedure for a <em>very</em> simple keyphrase search.</p>
  
  <p>I am trying to do a simple select on Name -using like %keyword%- then another select on Description -same keyword- and joining (union) the 2 selects. </p>
  
  <p>However, I get the error:</p>
  
  <pre><code>The ntext data type cannot be selected as DISTINCT because it is not comparable.
  </code></pre>
  
  <p>I tried using <code>UNION ALL</code> but that returned duplicate rows in certain instances (depending on the keyword/phrase).</p>
  
  <p>I also tried to work out using temp tables and selecting distinct on that, but that's where I got really confused.</p>
  
  <p><strong>Rules:</strong></p>
  
  <ul>
  <li>I can't change the data type</li>
  <li>I need the rows from select on the 'Name' to be above the rows from the select on the 'Description'</li>
  <li>I can only use 1 stored procedure, and can't change the data adapter, as I'm plugging it into a system I have no control over.</li>
  </ul>
  
  <p><strong>Further info:</strong></p>
  
  <p>Table Columns (the important 2 I am working with are Name and Description):</p>
  
  <pre><code>ProductId   int
  Name    varchar(255)
  Introduction    varchar(255)
  Description ntext
  Material    ntext
  Colour  varchar(255)
  Active  bit
  Dimensions  varchar(255)
  Photo   varchar(255)
  Price   decimal(10, 2)
  DisplayOrder    int
  ProductReference    varchar(255)
  CategoryId  int
  FriendlyURL varchar(1000)
  </code></pre>
  
  <p>SQL:</p>
  
  <pre><code>(SELECT        Products.ProductId, Name, Introduction, Description, Active, 
              Material, Colour, Dimensions, Photo, Price, DisplayOrder, FriendlyURL,
              ProductReference, Categories_Products_Lookup.CategoryId
  FROM            Products INNER JOIN
                  Categories_Products_Lookup ON 
                  Products.ProductId = Categories_Products_Lookup.ProductId
  WHERE           Active = 1 AND tProduct.Name like '%'+@Keyword+'%')
  UNION
  (SELECT        Products.ProductId, Name, Introduction, Description, Active, 
              Material, Colour, Dimensions, Photo, Price, DisplayOrder, FriendlyURL,
              ProductReference, Categories_Products_Lookup.CategoryId
  FROM            ProductsINNER JOIN
                  Categories_Products_Lookup ON 
                  Products.ProductId = Categories_Products_Lookup.ProductId
  WHERE           Active = 1 AND Products.Description like '%'+@Keyword+'%')
  </code></pre>
  
  <p>Any help getting out a table of distinct rows would be really appreciated. Also, explaining to me as a Layman would be great. :)</p>