>>Hi,
>>
>>I'm doing a select that contains a text field and trying to do a group by. SQL telle me I can't have text fields in the group clause. Fine- I take it out of the group clause and SQL now tells me that the statement can't run because the field isn't in an aggregate function or the group by clause!
>>
>>So - what the heck does it want?
>>
>>thanks,
>
>You don't actually want to group by the text data though, do you?
>
>What you are telling it to do is give you the contents of a field, but you are creating a group. So, say there are 10 records in your group, it doesn't know which text field to give you.
>
>What data do you want? If you have:
>
>ID MyTEXT
>1 This is some
>1 text that is stored
>1 in these record
>2 but how would sql
>2 know which text
>2 to give you, since there
>2 is more than one record in a group
>
>Then you try to:
>
>SELECT MyTEXT FROM MyTable GROUP BY ID
>
>It can do that... since text isn't in the group for the group with ID = 1 what text would you want?
>
>So, perhaps if you give us an idea of your table, and the results you want we could help ya!
>
>BOb

Bob - we're not communicating - actually, I've fixed this another way - so the point is mute for this particular incident - but in my opinion, SQL server has a big flaw in requiring every field in the group by - because in JOINED selects, you do NOT have the case you are indicating, but something more like:

ID OTHERFIELD MyTEXT
--------------------------
1 A This is some text
1 B This is some text
1 C This is some text
2 A This is some other text
2 B This is some other text
2 C This is some other text
2 D This is some other text

thus the need to group on ID but still return only the MYTEXT that applies to the ID.

thanks,