We help IT Professionals succeed at work.

T-SQL: The text data type cannot be selected as DISTINCT because it is not comparable

Hi:

Below is my code and the error message that I'm getting with using DISTINCT.

Besides using varchar(MAX), which isn't working, what else can be done in the syntax to allow for DISTINCT?

Thanks!

Software Engineer

Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.

select DISTINCT SOP30200.CUSTNMBR, sy01200.EmailToAddress
FROM sop30200 left outer join sy01200 on sop30200.CUSTNMBR = sy01200.Master_ID
where Master_ID like 'AAR%'

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Commented:
what version of sql server?

Sql server 2000:

select DISTINCT SOP30200.CUSTNMBR,  cast(sy01200.EmailToAddress AS VARCHAR(4000))

Open in new window


SQL Server 2005 and SQL Server 2008 (and later)
select DISTINCT SOP30200.CUSTNMBR,  cast(sy01200.EmailToAddress AS VARCHAR(MAX))

Open in new window


Other than that, change the data types of the columns
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Quite correct overall, but you should use VARCHAR( 8000) in the first version, not 4000.