group by ntext?

Question

Post reply

group by ntext?

Gail-378561

SSCommitted

Points: 1602
More actions
April 9, 2008 at 9:10 am

#379705

is there anyway around the inability to group by ntext in sql 2005?
I cannot change the datatype I am working with a 3rd party DB and I only have read only access via the ole adapter.
I have tried casting and converting the field to different datatypes but i can't seem to fine a way to get the field to group by since it is an ntext field.
Help is appreciated
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Do you have multiple rows with the same ntext data?

Also, if you can, please include a copy of the text of your query.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

And a sample of what's in the NTEXT column please... it's hard for me to imagine why you'd want to group on something that can be 4000 bytes long...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

steveb. SSC-Forever Points: 46733 More actions · Answer 3

try using

group by cast(words as varchar(max))

though depending on how long the text is this could be very inefficient

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Steve's suggestion is good, but use NVARCHAR(MAX) instead... 3rd party either made the original column NTEXT for a reason or because they're stupid... you have to account for either eventuality. 😉

And, still, I'd like to see a half dozen rows of info coming from the NTEXT column... GROUP BY something as potentially large as NTEXT is just... suidcide.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail-378561 SSCommitted Points: 1602 More actions · Answer 5

here is my query the field is a comment field commentsmc

SELECT [CONTACT TaskOrdersmc] AS TaskOrdersmc, [CONTACT PlaceofPerformaceSMC] AS PlaceofPerformaceSMC,

[CONTACT TaskordpercentSMC] AS TaskordpercentSMC, [CONTACT SpecialitySMC] AS SpecialitySMC,

[CONTACT PerfPeriodStartSMC] AS PerfPeriodStartSMC, [CONTACT SchStartDateSMC] AS SchStartDateSMC,

[CONTACT PerPeriodEndSMC] AS PerPeriodEndSMC, [CONTACT PrivilegeNonPrSMC] AS PrivilegeNonPrSMC,

[CONTACT FilledbySchDateSMC] AS FilledbySchDateSMC, [CONTACT PositionSMC] AS PositionSMC, [CONTACT FTECountSMC] AS FTECountSMC,

[CONTACT Contact] AS Contact, [CONTACT HireDateSMC] AS HireDateSMC, [CONTACT TermDateSMC] AS TermDateSMC,

[CONTACT SentToSMC] AS SentToSMC, [CONTACT SentToDate] AS SentToDate, [CONTACT ddldelayComments] AS ddldelayComments, [highlight=#ffff11][CONTACT CommentSMC] AS CommentSMC[/highlight],

[CONTACT AwardDateSMC] AS AwardDateSMC, [CONTACT ReplaceCon1SMC] AS ReplaceCon1SMC,

[CONTACT Replace1ConStartDateSMC] AS Replace1ConStartDateSMC, [CONTACT Replace1conTermDatesmc] AS Replace1conTermDatesmc,

[CONTACT Replacement2smc] AS Replacement2smc, [CONTACT Replacement2StartDatesmc] AS Replacement2StartDatesmc,

[CONTACT Replacement2TermDatesmc] AS Replacement2TermDatesmc, [CONTACT Replacement3smc] AS Replacement3smc,

[CONTACT Replacement3Startsmc] AS Replacement3Startsmc, [CONTACT Replacement3Term] AS Replacement3Term,

[CONTACT PeriodStartSMC] AS PeriodStartSMC, [CONTACT PeriodEndSMC] AS PeriodEndSMC, CASE WHEN [CONTACT Contact] IS NOT NULL AND [CONTACT Contact] not LIKE 'none' + '%' THEN 1 ELSE 0 END AS contactcount,

count ([Contact PositionSMC]) as positioncount,

CASE WHEN [CONTACT ReplaceCon1SMC] IS NOT NULL AND

[CONTACT ReplaceCon1SMC] NOT LIKE 'none' + '%' THEN 1 ELSE 0 END AS replace1count, CASE WHEN [CONTACT Replacement2SMC] IS NOT NULL AND

[CONTACT Replacement2SMC] NOT LIKE 'none' + '%' THEN 1 ELSE 0 END AS replace2count, CASE WHEN [CONTACT Replacement3SMC] IS NOT NULL AND

[CONTACT Replacement3SMC] NOT LIKE 'none' + '%' THEN 1 ELSE 0 END AS replace3count

FROM VRP_CONTACT

GROUP BY [CONTACT TaskOrdersmc], [CONTACT PlaceofPerformaceSMC], [CONTACT TaskordpercentSMC], [CONTACT SpecialitySMC],

[CONTACT PerfPeriodStartSMC], [CONTACT SchStartDateSMC], [CONTACT PerPeriodEndSMC], [CONTACT PrivilegeNonPrSMC],

[CONTACT FilledbySchDateSMC], [CONTACT PositionSMC], [CONTACT FTECountSMC], [CONTACT Contact], [CONTACT HireDateSMC], [highlight=#ffff11][CONTACT CommentSMC], [/highlight]

[CONTACT TermDateSMC], [CONTACT SentToSMC], [CONTACT SentToDate], [CONTACT ddldelayComments],

[CONTACT AwardDateSMC], [CONTACT ReplaceCon1SMC], [CONTACT Replace1ConStartDateSMC], [CONTACT Replace1conTermDatesmc],

[CONTACT Replacement2smc], [CONTACT Replacement2StartDatesmc], [CONTACT Replacement2TermDatesmc], [CONTACT Replacement3smc],

[CONTACT Replacement3Startsmc], [CONTACT Replacement3Term], [CONTACT PeriodStartSMC], [CONTACT PeriodEndSMC] HAVING ([CONTACT PeriodStartSMC] >= ?) AND ([CONTACT PeriodEndSMC] <= ?) AND ([CONTACT TaskOrdersmc] LIKE ? + '%') ORDER BY [CONTACT TaskOrdersmc],[CONTACT Contact]

Gail-378561 SSCommitted Points: 1602 More actions · Answer 6

ok i added the cast as suggested i was not putting that in both the query and the group by

If there are suggestions on how to improve i have an open ear

Thanks

GSquared SSC Guru Points: 260824 More actions · Answer 7

I may be missing something here. I don't see any aggregate functions in this query. No Sum, Count, Max, Min, Avg, etc. Without those, are you just using Group By to eliminate duplicate rows?

If so, get rid of the Group By and add the word "distinct" right after "select".

Or am I just missing the aggregate functions?

Edit: And replace "having" with "where".

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail-378561 SSCommitted Points: 1602 More actions · Answer 8

yeah you missed them

count ([Contact PositionSMC]) as positioncount,

CASE WHEN [CONTACT ReplaceCon1SMC] IS NOT NULL AND

[CONTACT ReplaceCon1SMC] NOT LIKE 'none' + '%' THEN 1 ELSE 0 END AS replace1count, CASE WHEN [CONTACT Replacement2SMC] IS NOT NULL AND

[CONTACT Replacement2SMC] NOT LIKE 'none' + '%' THEN 1 ELSE 0 END AS replace2count, CASE WHEN [CONTACT Replacement3SMC] IS NOT NULL AND

[CONTACT Replacement3SMC] NOT LIKE 'none' + '%' THEN 1 ELSE 0 END AS replace3count

GSquared SSC Guru Points: 260824 More actions · Answer 9

Then I think you're stuck with the query as-is (with the cast to varchar(max), of course).

With the table structure, some sample data, etc., it might be possible to improve on it, but as it is, I don't see any obvious improvements to make.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

Gail (4/9/2008)
here is my query the field is a comment field commentsmc

You're trying to group on a comment field? :blink: As I've asked a couple of times now, please post a couple of rows of data on this column... there's gotta be a better way...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail-378561 SSCommitted Points: 1602 More actions · Answer 11

thanks, yeah i think the 3rd party people messed up with they made all comment fields ntext!

there is not much to show you for the data the comments field is just that random comments about orders etc.

Thanks for the help!!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

Ah... thanks for the explanation, Gail... I just figured it out... the reason they need to do the GROUP BY on a comments column is because they're writing an aggregate query... and they think they need to do it all in one query so they think they need to include comments column in the GROUP BY... otherwise, it squawks about the non-aggregated column not being in the GROUP BY list.

A better way to do this is to do the aggregate as a derived table and, using some unique columns from the derived table, join the table having the comment column to the derived table.

Grouping by a long character based column is going to be a killer on performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)