How to elegantly write SQL ORDER BY (which is unacceptable in an inline query), but necessary for GROUP BY aggregation?

Question

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

I require comments to be ordered sequentially, hence the ORDER BY requirement.

The query that I have has a terrible TOP operator in it to allow ORDER BY to work for an aggregate function, otherwise the comments will not be in a specific 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

... , , CommentDate ?

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

: . , , , , SELECT TOP 100 PERCENT, , , , .

Answer 2

SQL Server 2008, 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