Group by on the text colum throws error

Hi ,

I have this query

paprojnumber is varchar
patx500 is text
palineitemseq is int

select Paprojnumber,Patx500,max(palineitemseq) from pa02101,pa01601
where
pa02101.pabillnoteidx=pa01601.pabillnoteidx group by
paprojnumber,patx500

it throws this error
Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.

Thanks a lot for your help.

AJHere it means exactly what the error says. You cannot sort on a text field
(or NText field), which is what your "group by" code is trying to do.

"AJ" <aj70000@.hotmail.com> wrote in message
news:6097f505.0409300838.a81c800@.posting.google.co m...
> Hi ,
> I have this query
> paprojnumber is varchar
> patx500 is text
> palineitemseq is int
> select Paprojnumber,Patx500,max(palineitemseq) from pa02101,pa01601
> where
> pa02101.pabillnoteidx=pa01601.pabillnoteidx group by
> paprojnumber,patx500
> it throws this error
> Server: Msg 306, Level 16, State 2, Line 1
> The text, ntext, and image data types cannot be compared or sorted,
> except when using IS NULL or LIKE operator.
> Thanks a lot for your help.
> AJ|||On Thu, 30 Sep 2004 18:24:58 +0100, Robin Tucker wrote:

> Here it means exactly what the error says. You cannot sort on a text field
> (or NText field), which is what your "group by" code is trying to do.

You can, however, group by an expression using it:

select Paprojnumber,Patx500,max(palineitemseq)
from pa02101,pa01601
where pa02101.pabillnoteidx=pa01601.pabillnoteidx
group by paprojnumber,convert(varchar(50),patx500)