title: Avoiding duplicates using stuff function and for xml path
body: <p>I'm having a problem with duplicates using mysql server with stuff function. The database contains few million records and using distinct is out of question (it seems that the query does not even process).</p>
  
  <p>This is my database structure:</p>
  
  <pre><code>PersonID    Freetext    Importance
  PersonID    Freetext    Importance
  PersonID    Freetext    Importance
  PersonID    Freetext    Importance
  PersonID    Freetext    Importance
  </code></pre>
  
  <p>I have been using following structure for this query. The query works fine (regarding stuff function), but it returns duplicates. For example, if there are five ID's which are the same, the query returns five rows:</p>
  
  <pre><code>SELECT PersonID, Importance, Freetext = STUFF(
               (SELECT '~' + Freetext
                FROM TABLE t1
                WHERE t1.PersonID = t2.PersonID
                FOR XML PATH (''))
               , 1, 1, '') from TABLE t2
  group By PersonID, Importance
  order by Importance
  </code></pre>
  
  <p>How to avoid this?</p>
  
  <p>I would like to have all the freetexts that belong to the same PersonID stuffed together to one single row, ordering the stuffing so that the row that has smallest importance would have it's freetext stuffed first.</p>