StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Distinct keyword in assignment statement
text
Body
copied!<p>I have a query that works:</p> <pre><code>DECLARE @ProductID int SET @ProductID = '1234' SELECT DISTINCT TOP 12 a.ProductID FROM A a WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE b.ProductID = @ProductID) AND a.ProductID != @ProductID </code></pre> <p>It returns a list of 12 product numbers, all unique.</p> <p>I need to store these results in a variable, comma separated, because that's what 3rd party stored procedure needs. So I have this:</p> <pre><code> DECLARE @ProductID int DECLARE @relatedprods varchar(8000) SET @ProductID = '1234' SET @relatedprods = '' SELECT TOP 12 @relatedprods = @relatedprods + CONVERT(VARCHAR(20), a.ProductID) + ', ' FROM A a WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE B.ProductID = @ProductID) AND a.ProductID != @ProductID SELECT @relatedprods </code></pre> <p>Now, none of these are distinct, but it is returning 12 rows.</p> <p>Now I add the 'distinct' back in, like in the first query:</p> <pre><code> DECLARE @ProductID int DECLARE @relatedprods varchar(8000) SET @ProductID = '1234' SET @relatedprods = '' SELECT DISTINCT TOP 12 @relatedprods = @relatedprods + CONVERT(VARCHAR(20), a.ProductID) + ', ' FROM A a WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE B.ProductID = @ProductID) AND a.ProductID != @ProductID SELECT @relatedprods </code></pre> <p>Only one product is returned in the comma separated list! Does 'distinct' not work in assignment statements? What did I do wrong? Or is there a way to get around this?</p> <p>Thanks in advance!</p> <p>CONCLUSION:</p> <p>I have no idea what causes this problem, though the guess proposed seems logical. I was able to solve this problem via sub-query, and am posting it so others can see the solution:</p> <pre><code> DECLARE @ProductID int DECLARE @relatedprods varchar(8000) SET @ProductID = '1234' SET @relatedprods = '' SELECT @relatedprods = @relatedprods + CONVERT(VARCHAR(20), c.ProductID) + ',' FROM (SELECT DISTINCT TOP 12 a.ProductID FROM A a WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE B.ProductID = @ProductID) AND a.ProductID != @ProductID ) c SET @relatedprods = SUBSTRING(@relatedprods, 0, LEN(@relatedprods)) SELECT @relatedprods </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload