5

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?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mohammadreza
  • 3,139
  • 8
  • 35
  • 56

1 Answers1

4

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,

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hila DG
  • 688
  • 4
  • 12
  • 1
    This is actually interesting. I've never looked into it, but this appears to be a difference in `GROUP BY` vs `DISTINCT`. The execution plan changes the order in which it calculates the variable value... – ZLK Jul 26 '16 at 06:12
  • Thank you, i know Concatenate operator + act as aggregate function but why concatenate operator with Distinct act as Max()? – Mohammadreza Jul 26 '16 at 06:21
  • Your rewrite still relies on behaviour that is execution plan dependent and not guaranteed. The only safe way is to use some other method such as `xml path`. – Martin Smith Jul 26 '16 at 06:22
  • @Mohammadreza - don't know, I tried and this is what I've got :) I saw a note about the "sort" and that's why I think it happened. – Hila DG Jul 26 '16 at 06:26