December 8, 2009 at 7:31 am
I have an application that accepts submissions for a conference. A submission can have multiple people from the same company or school on the submission. I am giving the end users the ability to search by company or school and I only want to display the submission once even if the have multiple people from that company on the submission. The associative table has a unique field and I got around this by selecting the max row when someone from that company is searched. However, I need to group by for the rest of the fields and one of the fields is a text field and I can not group by that field. I have tried converting it as a varchar(4000) but then it doesn't see the fieldname in the select query. Here is an example of my query. The text field is highlighted.
set @ConID= 3
set @aff = 'company'
select max(idAssocTable),idforSubmission, Title,abstract ,
from submissionTable inner join PresenterTable id=id
inner join Associative table on presenter ID = associative id
...other joins for diff fields=
where PresentersAffiliation = @aff and SubConference_conID = @ConID and subAccepted = 1
group by subID, subTitle, -->>> I tried to convert this as varchar max -->>subabstract ...other fields.
If I take out the text field my query works as intended. I just need to know how to convert the text field in the group by statement so it is still seen as being in the select. I have tried convert and cast in the group by and select.
December 8, 2009 at 7:49 am
It seems that your formatting has gone a little astray in the post.
Is the text column subTitle ?
December 8, 2009 at 8:07 am
The text field is subabstract sorry about the formatting..I thought I highlighted it.
December 8, 2009 at 9:05 am
Change the TEXT column in the table to a VARCHAR(MAX) column.
If you cannot do this (and Microsoft recommends that you do), then instead convert it to a VARCHAR(MAX) in your query and GROUP BY on that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 8, 2009 at 9:12 am
I really wish that I could change the table structure. I am not sure why it is set up this way, none of the data is that large in the field. I have converted it to varchar max in the query but then it doesn't see it in the group by.
I have tried it like this Convert(nvarchar(4000), subabstract)
and Convert(nvarchar(4000), subabstract) as subabstract but sql server does not see the fieldname when I do it this way.
Do I have the syntax screwed up for this? I have also tried the cast as to varchar max but still not seeing it.
Thanks for your help with this.
December 8, 2009 at 9:16 am
Sorry SS I did get the query to work. I just pasted it in the select and subquery the same way which I thought I have tried earlier....but it worked this time....Thanks for your help You saved my forehead from additional banging.
December 8, 2009 at 9:22 am
jhopkins-841253 (12/8/2009)
I really wish that I could change the table structure. I am not sure why it is set up this way, none of the data is that large in the field. I have converted it to varchar max in the query but then it doesn't see it in the group by.I have tried it like this Convert(nvarchar(4000), subabstract)
and Convert(nvarchar(4000), subabstract) as subabstract but sql server does not see the fieldname when I do it this way.
Do I have the syntax screwed up for this? I have also tried the cast as to varchar max but still not seeing it.
Like this:
GROUP BY subID, subTitle, CONVERT(VARCHAR(MAX), subabstract), ...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply