Porting PostgreSQL string_agg to SQL Server, problem with ORDER BY

Question

In PostgreSQL, there is this very useful string_agg function, that allows query like:

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

Similarly, this can be done in MySQL using group_concat.

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??).

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:

SELECT
    contacts.first_name,
    contacts.last_name,
    (
        SELECT
            dbo.string_agg(number, ', ')
        FROM
            (
                SELECT TOP <some really large number>
                    number
                FROM
                    phones
                WHERE
                    phones.contact_id = contacts.id
                ORDER BY
                    phones.priority
            ) AS phones
    ) AS phone_numbers
FROM
    contacts

Is there a better workaround? And yes, I have read Is order by clause allowed in a subquery, and this is, dare I say, a valid use case of ORDER BY in subquery.

score 0 · Answer 1 · answered Apr 26 '11 at 06:30

0

Your best bet query will not return the results you want. When ORDER BY is used with TOP in a subquery like that, the ORDER BY is only used to evaluate the top condition. It doesn't actually order the data for you.

answered Apr 26 '11 at 06:30

Jared Harding

4,942
2
17
14

I have replaced "TOP 100 PERCENT" with "TOP ", and that seems to be working. – sayap Apr 26 '11 at 06:41

score 0 · Accepted Answer · answered May 21 '11 at 01:06

Looks like there is no better alternative than using a subquery with TOP <some really large number> to get around SQL Server's limitation:

SELECT
    contacts.first_name,
    contacts.last_name,
    (
        SELECT
            dbo.string_agg(number, ', ')
        FROM
            (
                SELECT TOP <some really large number>
                    number
                FROM
                    phones
                WHERE
                    phones.contact_id = contacts.id
                ORDER BY
                    phones.priority
            ) AS phones
    ) AS phone_numbers
FROM
    contacts

Porting PostgreSQL string_agg to SQL Server, problem with ORDER BY

2 Answers2