SQL concat strings with same identifier and latest date

Question

I am looking for help to display messages from my applications. I have a query that takes the lines of each message and combines them together to form a sentence. However, some of my messages have two or more messages, in this case I only want the message with the most recent date. My current code displays all the messages in a sentence, combining the line numbers together for each message number and calling them in order by MSG_NUM. I added ordering by MSG_START_DATE, but, as expected, still gives me both. How can I make max for each line of concatenated messages?

Here are the fields I'm working with:

MSG_NUM | MSG_START_DATE  | MSG_LINE_NUM | MSG_TEXT

1       | 2010-01-15      | 1            | Invalid operation
1       | 2010-01-15      | 2            | try again
1       | 2014-02-21      | 1            | Invalid input
1       | 2014-02-21      | 2            | try again

Here is my current code:

Select distinct ST2.[MSG_NUM],
substring(
    (
        (Select ' '+LTRIM(RTRIM(ST1.[MSG_TEXT]))  AS [text()]
        From database..messages ST1 
        Where ST1.[MSG_NUM] = ST2.[MSG_NUM]
        ORDER BY ST1.[MSG_START_DATE], ST1.[MSG_LINE_NUM]
        For XML PATH (''),root('xmlchar'), type).value('/xmlchar[1]','varchar(max)')
    ), 2, 2000) [Message]
From database..messages ST2 order by ST2.[MSG_NUM]

And here is the result that I get:

1

:

1

, ?

Answer 1

?

Select distinct ST2.[MSG_NUM],
substring(
    (
        (Select ' '+LTRIM(RTRIM(ST1.[MSG_LINE_NUM]))  AS [text()]
        From database..messages ST1 
        Where ST1.[MSG_NUM] = ST2.[MSG_NUM]
          AND ST1.[MSG_START_DATE] = (SELECT MAX(ST3.[MSG_START_DATE])
                                      FROM database..messages ST3
                                      WHERE ST2.[MSG_NUM] = ST3.[MSG_NUM]
                                     )
        ORDER BY ST1.[MSG_LINE_NUM]
        For XML PATH (''),root('xmlchar'), type).value('/xmlchar[1]','varchar(max)')
    ), 2, 2000) [Message]
From database..messages ST2 order by ST2.[MSG_NUM]

, ,

WITH maxd
AS (
    SELECT *
        ,MAX(MSG_START_DATE) OVER (PARTITION BY [MSG_NUM]) AS maxdate
    FROM DATABASE..messages
    )
    ,filter
AS (
    SELECT *
    FROM maxd
    WHERE MSG_START_DATE = maxdate
    )
SELECT DISTINCT ST2.[MSG_NUM]
    ,substring((
            (
                SELECT ' ' + LTRIM(RTRIM(ST1.[MSG_LINE_NUM])) AS [text()]
                FROM filter ST1
                WHERE ST1.[MSG_NUM] = ST2.[MSG_NUM]
                ORDER BY ST1.[MSG_LINE_NUM]
                FOR XML PATH('')
                    ,root('xmlchar')
                    ,type
                ).value('/xmlchar[1]', 'varchar(max)')
            ), 2, 2000) [Message]
FROM filter ST2
ORDER BY ST2.[MSG_NUM]