title: Porting PostgreSQL string_agg to SQL Server, problem with ORDER BY
body: <p>In PostgreSQL, there is this very useful string_agg function, that allows query like:</p>
  
  <pre><code>SELECT
      contacts.first_name,
      contacts.last_name,
      (
          SELECT
              string_agg(number, ', ' ORDER BY phones.priority)
          FROM
              phones
          WHERE
              phones.contact_id = contacts.id
      ) AS phone_numbers
  FROM
      contacts
  </code></pre>
  
  <p>Similarly, this can be done in MySQL using group_concat.</p>
  
  <p>Now, I am trying to port this into SQL Server as a CLR User-Defined Aggregate. The code itself is not a problem, as there are thousands of examples all over the web to create this particular aggregate (which beg the question: why is it not part of SQL Server already??).</p>
  
  <p>The problem is, I can't find a clean way to apply the ORDER BY, since SQL Server not only doesn't support ORDER BY within aggregate function, it also forbids the use of ORDER BY in subquery. My best bet is this:</p>
  
  <pre><code>SELECT
      contacts.first_name,
      contacts.last_name,
      (
          SELECT
              dbo.string_agg(number, ', ')
          FROM
              (
                  SELECT TOP &lt;some really large number&gt;
                      number
                  FROM
                      phones
                  WHERE
                      phones.contact_id = contacts.id
                  ORDER BY
                      phones.priority
              ) AS phones
      ) AS phone_numbers
  FROM
      contacts
  </code></pre>
  
  <p>Is there a better workaround? And yes, I have read <a href="https://stackoverflow.com/questions/2101908/is-order-by-clause-allowed-in-a-subquery">Is order by clause allowed in a subquery</a>, and this is, dare I say, a valid use case of ORDER BY in subquery.</p>