Select Distinct - Incorrect result

[Pat L]

I have a function that is designed to return a variable that contains
concatenated values from a partinular field in the returned rows:

DECLARE @output varchar(8000)
SELECT
@output =
CASE
WHEN @output IS NULL THEN CAST(TSD.ScheduledTime AS
varchar(4))
ELSE @output+ ', '+ ISNULL(CAST(TSD.ScheduledTime AS
varchar(4)),'')
END
FROM TSD
WHERE ClientGUID = 2000001447020001 AND
ParentGUID = 6000006684068001
Select @output

The variable returned with this code contains:

"1200, 1400, 1200, 1400"

I want to only get the unique values so that the variable returns "1200,
1400". Seems simple enough just to add DISTINCT to the SELECT statement.
However, what is returned is simply "1400".

I cannot figure out why that is the case. Is there any explanation to this
result?

Side note: I can work around this by using a cursor but I would like to
know why DISTINCT does not work.

Many thanks in advance for any help that can be provided!

Pat

[Erland Sommarskog]

Pat L (is****@telusZ.net) writes:

I have a function that is designed to return a variable that contains
concatenated values from a partinular field in the returned rows:

DECLARE @output varchar(8000)
SELECT
@output =
CASE
WHEN @output IS NULL THEN CAST(TSD.ScheduledTime AS
varchar(4))
ELSE @output+ ', '+ ISNULL(CAST(TSD.ScheduledTime AS
varchar(4)),'')
END
FROM TSD
WHERE ClientGUID = 2000001447020001 AND
ParentGUID = 6000006684068001
Select @output

The result of this query is undefined. You may get what you want, you
may get something else. It appears that when you use DISTINCT, the latter
applies.

See http://support.microsoft.com/default.aspx?scid=287515. Pay
particular attention to the first paragraph under CAUSE.

So a cursor is the way to go this time.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Pat L]

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1...

<snip> The result of this query is undefined. You may get what you want, you
may get something else. It appears that when you use DISTINCT, the latter
applies.

See http://support.microsoft.com/default.aspx?scid=287515. Pay
particular attention to the first paragraph under CAUSE.

So a cursor is the way to go this time.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks you so much for the quick response Erland!

I read the article but since it does not actually use or refer to DISTINCT,
and I don't use the order by clause, where is the connection?

Pat

[David Portas]

DISTINCT performs an implict sort of the table with the same effect.

From the doc that Erland posted: "The correct behavior for an aggregate
concatenation query is undefined". In other words the behaviour your query
assumes is an undocumented feature and there are no guarantees that the
result will always be what you expect.

The result you described with SELECT DISTINCT is actually correct according
to BOL:

SELECT @local_variable is usually used to return a single value into the
variable. It can return multiple values if, for example, expression is the
name of a column. If the SELECT statement returns more than one value, the
variable is assigned the last value returned.

http://msdn.microsoft.com/library/en...a-ses_978l.asp

Returning a comma-delimited list from a table is a presentational task which
may be better done in your client application or reporting tool.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Pat L (is****@telusZ.net) writes:

I read the article but since it does not actually use or refer to
DISTINCT, and I don't use the order by clause, where is the connection?

There is no connection to DISTINCT, but there is a connection to things
like "SELECT @x = @x + col FROM tbl" in general, and the article says
that a statement like this has no defined result.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Pat L]

Thanks David (and again to Erland).

Pat

"David Portas" <RE****************************@acm.org> wrote in message
news:3r********************@giganews.com...

DISTINCT performs an implict sort of the table with the same effect.

From the doc that Erland posted: "The correct behavior for an aggregate
concatenation query is undefined". In other words the behaviour your query
assumes is an undocumented feature and there are no guarantees that the
result will always be what you expect.

The result you described with SELECT DISTINCT is actually correct according to BOL:

SELECT @local_variable is usually used to return a single value into the
variable. It can return multiple values if, for example, expression is the
name of a column. If the SELECT statement returns more than one value, the
variable is assigned the last value returned.

http://msdn.microsoft.com/library/en...a-ses_978l.asp

Returning a comma-delimited list from a table is a presentational task which may be better done in your client application or reporting tool.

--
David Portas
SQL Server MVP
--