Distinct on Text Column

How can I select distinct values from a table which has column datatype as
Text.
data sholud n't be truncated.
--
Regards,
Kassim.http://support.microsoft.com/kb/162032/en-us
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
> How can I select distinct values from a table which has column datatype as
> Text.
> data sholud n't be truncated.
> --
> Regards,
> Kassim.|||I do get these error, is there any other way to over come this.
Kassim.
"Jens Sü?meyer" wrote:

> http://support.microsoft.com/kb/162032/en-us
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
>
>|||I do get these error, is there any other way to over come this.
Kassim.
"Jens Sü?meyer" wrote:

> http://support.microsoft.com/kb/162032/en-us
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
>
>|||I do get these error, is there any other way to over come this.
Kassim.
"Jens Sü?meyer" wrote:

> http://support.microsoft.com/kb/162032/en-us
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
>
>|||Can you post some DDL and your query please.
"M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
news:517D91E0-FA6D-41EB-AD5C-3CAF4ED0F393@.microsoft.com...
>I do get these error, is there any other way to over come this.
> Kassim.
> "Jens Smeyer" wrote:
>|||Does the text column need to be part of the DISTINCT
operator, or can you be sure the text columns match if all
the other columns match? If you only need DISTINCT on
the other columns, one solution is to create a primary key or
unique column for the table. If myID is a unique
integer column, you could do something like this:
select * from myTable
where myID in (
select min(myID)
from myTable
group by col1, col2, col3
-- Do *not* include the text column in this list
)
If there are two different text column values for
the same (col1, col2, col3), you will get only one
of those rows.
If you need to determine if the text columns are unequal,
you could compare the first 8000 characters, or more if
you want:
select * from myTable
where not exists (
select * from myTable as Tcopy
where Tcopy.col1 = T.col1
and Tcopy.col2 = T.col2
..
and substring(Tcopy.textcol,1,8000) = substring(T.textcol,1,8000)
and substring(Tcopy.textcol,8001,8000) = substring(T.textcol,8001,8000)
and Tcopy.myID < T.myID
)
Steve Kass
Drew University
M Kassim wrote:

>How can I select distinct values from a table which has column datatype as
>Text.
>data sholud n't be truncated.
>|||Hi,
I have a table called comments, which has 2 columns commentID primarykey
and comment [Text datatype], now I would like
select distinct comment from comments.
Kassim.
---
"Steve Kass" wrote:

> Does the text column need to be part of the DISTINCT
> operator, or can you be sure the text columns match if all
> the other columns match? If you only need DISTINCT on
> the other columns, one solution is to create a primary key or
> unique column for the table. If myID is a unique
> integer column, you could do something like this:
> select * from myTable
> where myID in (
> select min(myID)
> from myTable
> group by col1, col2, col3
> -- Do *not* include the text column in this list
> )
> If there are two different text column values for
> the same (col1, col2, col3), you will get only one
> of those rows.
> If you need to determine if the text columns are unequal,
> you could compare the first 8000 characters, or more if
> you want:
> select * from myTable
> where not exists (
> select * from myTable as Tcopy
> where Tcopy.col1 = T.col1
> and Tcopy.col2 = T.col2
> ...
> and substring(Tcopy.textcol,1,8000) = substring(T.textcol,1,8000)
> and substring(Tcopy.textcol,8001,8000) = substring(T.textcol,8001,8000)
> and Tcopy.myID < T.myID
> )
> Steve Kass
> Drew University
> M Kassim wrote:
>
>sql