title: Can I sort data for an aggregate function?
body: <p>I have a custom CLR aggregate function. This function concats strings within a group. Now the question is, can I make this function process the data in some specific order or will it always be some random order the DB found suitable? I understand that for most mathematical aggregate functions (MIN, MAX, AVG etc.) it makes no difference in which order the data is processed, but let's say I want to concat strings alphabetically within a group is there something I can do to achieve this result?</p>
  
  <p>Note that it <strong>has</strong> to be an aggregate function (don't get mislead by the examples below) <strike>and that altering the existing CLR function is out of question (all it does is a basic string concat and nothing more)</strike>.</p>
  
  <p>I tested adding <code>ORDER BY</code> to the <code>SELECT</code> that contains the <code>GROUP BY</code>, but it produced no meaningful results.</p>
  
  <pre><code>SELECT
      user.Id, dbo.concat(cat.Name)
  FROM
      Users user
      JOIN Cats cat ON (cat.Owner = user.Id)
  GROUP BY user.Id
  ORDER BY user.Id, MAX(cat.Name) --kind of meaningless really
  </code></pre>
  
  <p>I also tried to <code>ORDER BY</code> the table that contains the data which I want to concat before doing a <code>JOIN</code>, but the result was the same.</p>
  
  <pre><code>SELECT
      user.Id, dbo.concat(cat.Name)
  FROM
      Users user
      JOIN (SELECT TOP 100 PERCENT /*hack*/ c.* FROM Cats c ORDER BY c.Name) cat ON (cat.Owner = user.Id)
  GROUP BY user.Id
  </code></pre>
  
  <p>Ordering data in a subquery and then doing a <code>GROUP BY</code> didn't work either.</p>
  
  <pre><code>SELECT
      t1.Id, dbo.concat(t1.Name)
  FROM
  (
      SELECT TOP 100 PERCENT /*hack*/
          user.Id, cat.Name
      FROM
          Users user
          JOIN Cats cat ON (cat.Owner = user.Id)
      ORDER BY user.Id, cat.Name
  ) t1
  GROUP BY t1.Id
  </code></pre>
  
  <p>I was kind of expecting that neither of those will work, but at least now no one can say I haven't tried anything.</p>
  
  <p>P.S. Yes, I have reasons not to use <code>FOR XML PATH</code>. If what I'm asking here cannot be done, I'll live with it.</p>