How to elegantly write a SQL ORDER BY (which is invalid in inline query) but required for aggregate GROUP BY?

Question

I have a simple query that runs in SQL 2008 and uses a custom CLR aggregate function, dbo.string_concat which aggregates a collection of strings.

I require the comments ordered sequentially hence the ORDER BY requirement.

The query I have has an awful TOP statement in it to allow ORDER BY to work for the aggregate function otherwise the comments will be in no particular order when they are concatenated by the function.

Here's the current query:

SELECT ID, dbo.string_concat(Comment) 
FROM (
    SELECT TOP 10000000000000 ID, Comment, CommentDate 
    FROM Comments 
    ORDER BY ID, CommentDate DESC
     ) x
GROUP BY ID

Is there a more elegant way to rewrite this statement?

Answer 1

So... what you want is comments concatenated in order of ID then CommentDate of the most recent comment?

Couldn't you just do

SELECT ID, dbo.string_concat(Comment)
FROM Comments
GROUP BY ID
ORDER BY ID, MAX(CommentDate) DESC

Edit: Misunderstood your objective. Best I can come up with is that you could clean up your query a fair bit by making it SELECT TOP 100 PERCENT, it's still using a top but at least it gets around having an arbitrary number as the limit.

Answer 2

Since you're using sql server 2008, you can use a Common Table Expression:

WITH cte_ordered (ID, Comment, CommentDate)
AS
(
    SELECT ID, Comment, CommentDate 
    FROM Comments 
    ORDER BY ID, CommentDate DESC
)
SELECT ID, dbo.string_concat(Comment) 
FROM cte_ordered
GROUP BY ID