none
The text data type cannot be selected as DISTINCT because it is not comparable.

    Question

  • I'm having a problem building a query for an application I'm writing. Basically, there are a bunch of records in a table, and some of them may have the same identifier code. I only want one of each identifier code. The table looks like this:

     

    Memo

    ---------

    Msg_Identifier         varchar(60)

    Memo_ID               int (pk)

    Subject                  varchar(64)

    Message                text

    Priority                   int

    Deleted                  int

     

     

    My query:

     

    SELECT DISTINCT Msg_Identifier, Memo_ID, Subject, Message, Priority FROM Memo WHERE Deleted = '0'

     

     

    When I run this query, I get "The text data type cannot be selected as DISTINCT because it is not comparable."

     

    I only want Msg_Identifier to be the distinct field, and it's obviously not set up as text. I've used SELECT DISTINCT in the past but I don't ever recall hitting an issue like this. What's the best way to do it?

    Wednesday, May 28, 2008 2:50 PM

Answers

  • Convert the Message column to VARCHAR(MAX)

    Code Snippet

     

    CONVERT(VARCHAR(MAX), Message)

     

     

    Wednesday, May 28, 2008 3:02 PM
    Moderator
  • Distinct is applied across all the columns being selected, so if you have 3 columns in the select list, all 3 values are compared for distinctness:

     

    Code Snippet

    declare @table1 table

    (someid int,

    somedata varchar(10),

    someotherdata varchar(20))

    insert into @table1 select 1, 'test', 'test'

    insert into @table1 select 1, 'test', 'test1'

    insert into @table1 select 2, 'test', 'test'

    insert into @table1 select 2, 'test', 'test'

    select distinct someid, somedata, someotherdata

    from @table1

     

     

    Varchar(Max) is a LOB datatype and has a max size of 2GB. 
    Wednesday, May 28, 2008 4:19 PM
    Moderator

All replies

  • Can you provide a little sample data for input and expected output?

     

    • Proposed as answer by Haruno Kenobi Friday, June 2, 2017 7:05 PM
    • Unproposed as answer by Haruno Kenobi Friday, June 2, 2017 7:05 PM
    Wednesday, May 28, 2008 3:01 PM
    Moderator
  • Convert the Message column to VARCHAR(MAX)

    Code Snippet

     

    CONVERT(VARCHAR(MAX), Message)

     

     

    Wednesday, May 28, 2008 3:02 PM
    Moderator
  •  David Dye wrote:

    Convert the Message column to VARCHAR(MAX)

    Code Snippet

     

    CONVERT(VARCHAR(MAX), Message)

     

     

     

    That works. That caps it at 8000 characters right? Not really a big deal -- I don't imagine the data in that field will get to that point.

     

    Why does it care about the Message field, out of curiosity? I thought DISTINCT only worked on the column that follows it, and that there was something separate for selecting a full row as distinct.

     

    Wednesday, May 28, 2008 3:26 PM
  • Distinct is applied across all the columns being selected, so if you have 3 columns in the select list, all 3 values are compared for distinctness:

     

    Code Snippet

    declare @table1 table

    (someid int,

    somedata varchar(10),

    someotherdata varchar(20))

    insert into @table1 select 1, 'test', 'test'

    insert into @table1 select 1, 'test', 'test1'

    insert into @table1 select 2, 'test', 'test'

    insert into @table1 select 2, 'test', 'test'

    select distinct someid, somedata, someotherdata

    from @table1

     

     

    Varchar(Max) is a LOB datatype and has a max size of 2GB. 
    Wednesday, May 28, 2008 4:19 PM
    Moderator
  • .Great job
    Thursday, November 5, 2009 7:30 AM
  • Convert the Message column to VARCHAR(MAX)

    cheers for that

    SharePoint 2007 (Administration/Development) | Web developer and .NET DTS Programmer | .NET Framework 2.0/3.5 | SQL Server 2008/2005/2k
    Wednesday, November 2, 2011 2:36 AM