CODEWITHSUNDEEP
sqlsql-servert-sqldistinct
kevin mouton
kevin mouton

Reputation: 13

TSQL Distinct does not work

I have the following query and I try to keep a unique value for each GLOBAL_CONTENT_ID using the DISTINCT keyword. Unfortunately I cannot make it work. 

SELECT DISTINCT 
    CD.GLOBAL_CONTENT_ID, CD.DOWNLOAD_ID, PA.PHYSICAL_ASSET_ID
FROM 
    [CONTENT_DOWNLOAD] CD 
INNER JOIN 
    PHYSICAL_ASSET AS PA ON CD.GLOBAL_CONTENT_ID = PA.GLOBAL_CONTENT_ID
WHERE 
    CD.UPC = '00600753515501'
ORDER BY 
    CD.GLOBAL_CONTENT_ID

enter image description here

Any idea?

Thanks

Upvotes: 0

Views: 229

Answers (2)

Lock
Lock

Reputation: 5522

DISTINCT works on every column in the SELECT clause, not just a single column. If one of those columns has a different value, then the row is considered different and returned as another row. In your query, you are including 'PHYSICAL_ASSET_ID' which has a different value for each row which is why you are getting multiple rows.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

The DISTINCT keyword will ensure that no duplicate records appear in your result set. However, it makes no guarantee that a given column cannot have duplicate values across multiple records, if the combination of values in those records be distinct.

One option to get the distinct GLOBAL_CONTENT_ID values would be to use the following query:

SELECT DISTINCT CD.GLOBAL_CONTENT_ID
FROM [CONTENT_DOWNLOAD] CD 
INNER JOIN PHYSICAL_ASSET AS PA ON CD.GLOBAL_CONTENT_ID = PA.GLOBAL_CONTENT_ID
WHERE CD.UPC = '00600753515501'
ORDER BY CD.GLOBAL_CONTENT_ID

Upvotes: 3

Related Questions