Concatenating column values with distinct gets strange result [duplicate]

Question

I have a simple one column table with two values. I select it and concatenate values with distinct modifier but it just gets latest value. Am I in a misconception with DISTINCT?

DECLARE @table TABLE(Id int) 
DECLARE @result VARCHAR(MAX) = ''

INSERT @table VALUES(1), (2)

SELECT 
    @result = @result + CAST( Id AS VARCHAR(10)) + ','
FROM 
    @table

SELECT @result  --— output: 1,2,

-------same With distinct
SET @result = ''

SELECT DISTINCT @result = @result 
        + CAST( Id AS VARCHAR(10)) + ','
FROM @table
SELECT @result  --— expected output: 1,2, actual output: 2,    why?

Answer 1

A quick look in the execution plan (and some playing around) had shown me that SELECT DISTINCT also sorts, and therefor you get the maximum id.

For example, in the

INSERT @table VALUES(1),(2),(1),(4), (2), (3)

I would get the result 4 (because 4 is the highest one).

The solution? Place the "distinct" in a sub query like this:

SELECT     
    @result = @result 
        + CAST( Id AS VARCHAR(10)) + ','
FROM
    (SELECT DISTINCT id
     FROM @table) Q

Resulting in : 1, 2, 3, 4,