How to group memo field

[2969]

hi,

i have this query that performs grouping..

select table.field1, table1.field2 from table1 group by field1, field2.

field2 is a memo field so it does not allows grouping a memo field.. Is there a way I can workaround the situation.

I am using VB6, SQL server 8.0

 

[mrdenny]

There is no memo field type in SQL Server. I believe that memo is an access data type. I am assuming that you are talking about the text data type. The only way to group by a text field would be to convert the text field to a varchar field.

The purpose of the text field is to have a large amount of non-unique data in the field. If you have the same data in the text field you should considder a child table with a relationship.

Also how much data are you feeding into this field? If you are putting less than 8000 bytes in the field you should considder changing the field type from text to varchar so that you won't have these problems.

Text fields should only be used when you need to be able to stuff more than 8000 bytes into the field, or when your record length is approching the 8060 byte mark as you have a hard time working with text field data in stored procedures, or other code such as this problem.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[dbomrrsm]

you could CAST the memo field as varchar (2000) and see if you can then group by this.

select cast(mymemofield as varchar (2000))
from mytable
group by 1

not sure if it will work though.

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

if it is text type this will work:

select cast(something as varchar (2000)),count(cast(something as varchar (2000)))
from mytable
group by cast(something as varchar (2000))

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

or just

select cast(something as varchar (2000))
from mytable
group by cast(something as varchar (2000))

[blue]DBomrrsm[/blue]

 

[2969]

hi..the following statement worked..

select cast(something as varchar (2000)),count(cast(something as varchar (2000)))
from mytable
group by cast(something as varchar (2000))