title: Using distinct within for xml path without including in select
body: <p>The top query looks in a table that will only ever have one record per statement, so if a customer has 7 statements the number of rows should be 7 and the query would list them like so 1,2,3,4,5,6,7</p>
  
  <p>The bottom query looks in a table which will have the 7 statements like above, but more often than not, they will be split, so if there is 2 lines for each statement there would be 14, i.e. 1,1,2,2,3,3,4,4,5,5,6,6,7,7</p>
  
  <p>Now, what I'm trying to achieve is the following, the top query is fine as it is, however the bottom query needs to be made distinct. Look at result number 2 on the bottom image, I want this to come back as 1,2,3,4,5,6,7,8,9,10.</p>
  
  <p>If there was a particular instance where the statements went up to 8 and 4 respectively, i would want 1,2,3,4,5,6,7,8. Basically It is in most cases duplicating the statement numbers for the bottom query and I would like it to just do a distinct occross the whole result, however I have tried putting a distinct in and it complains that order by items must be contained in the select statement if contained within a distinct, this ruins my query.</p>
  
  <p>The ultimate aim of these two queries is to compare the top result set against the bottom and bring back only the ones that dont match (as that will mean I am missing a statement in the top queries table)</p>
  
  <pre><code>--Shows the each consolidated statement number that exisits for that particular customer reference number within the dbo.rss table.
  
  Select Main.cust_ref,
         Left(CAST(Main.consolidatedstatements as varchar(max)),Len(CAST(Main.consolidatedstatements as varchar(max)))-1) As "consolidatedstatements"
  From(Select distinct ST2.cust_ref, 
             (Select CAST(ST1.consolidated_stmt_num as varchar(max)) + ',' AS [text()]
              From dbo.rss ST1
              Where ST1.cust_ref = ST2.cust_ref
              ORDER BY ST1.cust_ref
              For XML PATH ('')) [consolidatedstatements]
       From dbo.rss ST2) [Main]
  
  
  --Shows the each consolidated statement number that exisits for that particular customer reference number within the dbo.SC table.
  Select Main.cust_ref,
         Left(CAST(Main.consolidatedstatements as varchar(max)),Len(CAST(Main.consolidatedstatements as varchar(max)))-1) As "consolidatedstatements"
  From(Select distinct ST2.cust_ref, 
             (Select  CAST(ST1.consolidated_stmt_num as varchar(max)) + ',' AS [text()]
              From dbo.SC ST1
              Where ST1.cust_ref = ST2.cust_ref
              ORDER BY ST1.cust_ref
              For XML PATH ('')) [consolidatedstatements]
       From dbo.SC ST2) [Main]
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/Cbjwr.png" alt="enter image description here"></p>