Solved

how to put a long text in a Group by clause

Posted on 2004-04-15
24
286 Views
Last Modified: 2007-12-19
SELECT     TOP 100 PERCENT dbo.tblIssues.Name AS [Issue Date], dbo.vwArticles_AllArticles.ContextType, dbo.vwArticles_AllArticles.ContextSubtype,
                      dbo.tblAuthors.Name AS Author, dbo.tblArticles.Title, tblArticlePages_1.PageNum
FROM         dbo.tblArticleAuthors INNER JOIN
                      dbo.tblAuthors ON dbo.tblArticleAuthors.AuthorID = dbo.tblAuthors.AuthorID INNER JOIN
                      dbo.tblArticles ON dbo.tblArticleAuthors.ArticleID = dbo.tblArticles.ArticleID INNER JOIN
                      dbo.tblContextSubtypes ON dbo.tblAuthors.ContextSubtypeID = dbo.tblContextSubtypes.ContextSubtypeID INNER JOIN
                      dbo.tblIssues ON dbo.tblArticles.IssueID = dbo.tblIssues.IssueID INNER JOIN
                      dbo.vwArticles_AllArticles ON dbo.tblArticles.ArticleID = dbo.vwArticles_AllArticles.ArticleID INNER JOIN
                      dbo.tblArticlePages tblArticlePages_1 ON dbo.tblArticles.ArticleID = tblArticlePages_1.ArticleID CROSS JOIN
                      dbo.tblArticlePages
GROUP BY dbo.tblIssues.SiteID, dbo.tblIssues.Name, dbo.tblIssues.IssueID, dbo.tblArticles.Title, dbo.tblAuthors.Name,tblArticlePages_1.PageNum,
                      dbo.vwArticles_AllArticles.ContextType, dbo.vwArticles_AllArticles.ContextSubtype, dbo.tblArticlePages.Content
HAVING      (dbo.tblIssues.SiteID = 1)
ORDER BY dbo.tblIssues.IssueID

This is my view . I have  one more table from which I have to get the content field . It is a Longtext
field. How can insert that field into this group by clause.
0
Comment
Question by:tech_question
  • 8
  • 8
  • 4
  • +3
24 Comments
 
LVL 34

Expert Comment

by:arbert
Comment Utility
You can't group by the statement unless you perform a CAST/CONVERT on it:

SELECT     TOP 100 PERCENT convert(varchar(8000),Content),dbo.tblIssues.Name AS [Issue Date], dbo.vwArticles_AllArticles.ContextType, dbo.vwArticles_AllArticles.ContextSubtype,
                      dbo.tblAuthors.Name AS Author, dbo.tblArticles.Title, tblArticlePages_1.PageNum
FROM         dbo.tblArticleAuthors INNER JOIN
                      dbo.tblAuthors ON dbo.tblArticleAuthors.AuthorID = dbo.tblAuthors.AuthorID INNER JOIN
                      dbo.tblArticles ON dbo.tblArticleAuthors.ArticleID = dbo.tblArticles.ArticleID INNER JOIN
                      dbo.tblContextSubtypes ON dbo.tblAuthors.ContextSubtypeID = dbo.tblContextSubtypes.ContextSubtypeID INNER JOIN
                      dbo.tblIssues ON dbo.tblArticles.IssueID = dbo.tblIssues.IssueID INNER JOIN
                      dbo.vwArticles_AllArticles ON dbo.tblArticles.ArticleID = dbo.vwArticles_AllArticles.ArticleID INNER JOIN
                      dbo.tblArticlePages tblArticlePages_1 ON dbo.tblArticles.ArticleID = tblArticlePages_1.ArticleID CROSS JOIN
                      dbo.tblArticlePages
GROUP BY convert(varchar(8000),Content),dbo.tblIssues.SiteID, dbo.tblIssues.Name, dbo.tblIssues.IssueID, dbo.tblArticles.Title, dbo.tblAuthors.Name,tblArticlePages_1.PageNum,
                      dbo.vwArticles_AllArticles.ContextType, dbo.vwArticles_AllArticles.ContextSubtype, dbo.tblArticlePages.Content
HAVING      (dbo.tblIssues.SiteID = 1)
ORDER BY dbo.tblIssues.IssueID



The only problem with this--you would lose anything over 8000characters....
0
 

Author Comment

by:tech_question
Comment Utility
I would need all the characters. What isthe alternative?
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
There really isn't one--that's just the way a text type works--straight from books online:

You cannot use GROUP BY or HAVING on ntext,text,image, or bit columns unless they are in a function that returns a value having another data type. Examples of such functions are SUBSTRING and CAST.



You could substring the text column into multiple 8000byte columns, but that would get really messy....
0
 

Expert Comment

by:pwang1973
Comment Utility
On the assumption that the first 8000 character are distinct for each text field.

You can group by convert(varchar(8000), textfield) then rejoin to
convert(varchar(8000), textfield) and get the the whole text field.

Hope this helps.
0
 

Author Comment

by:tech_question
Comment Utility
pwang - I did not get you. This is my query . Could you please elaborate as to how I can get the entire field.


select convert(varchar(8000),content) from tblArticlePages group by convert(varchar(8000),content)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>You could substring the text column into multiple 8000byte columns, but that would get really messy....<<
This is what arbert is referring to (plagiarizing mercilessly):

SELECT     TOP 100 PERCENT convert(varchar(8000),Content),dbo.tblIssues.Name AS [Issue Date], dbo.vwArticles_AllArticles.ContextType, dbo.vwArticles_AllArticles.ContextSubtype,
                      dbo.tblAuthors.Name AS Author, dbo.tblArticles.Title, tblArticlePages_1.PageNum
FROM         dbo.tblArticleAuthors INNER JOIN
                      dbo.tblAuthors ON dbo.tblArticleAuthors.AuthorID = dbo.tblAuthors.AuthorID INNER JOIN
                      dbo.tblArticles ON dbo.tblArticleAuthors.ArticleID = dbo.tblArticles.ArticleID INNER JOIN
                      dbo.tblContextSubtypes ON dbo.tblAuthors.ContextSubtypeID = dbo.tblContextSubtypes.ContextSubtypeID INNER JOIN
                      dbo.tblIssues ON dbo.tblArticles.IssueID = dbo.tblIssues.IssueID INNER JOIN
                      dbo.vwArticles_AllArticles ON dbo.tblArticles.ArticleID = dbo.vwArticles_AllArticles.ArticleID INNER JOIN
                      dbo.tblArticlePages tblArticlePages_1 ON dbo.tblArticles.ArticleID = tblArticlePages_1.ArticleID CROSS JOIN
                      dbo.tblArticlePages
GROUP BY       convert(varchar(8000),Content),
                      convert(varchar(8000), Substring(Content, 8001, 8000),
                      convert(varchar(8000), Substring(Content, 16001, 8000),
                      convert(varchar(8000), Substring(Content, 24001, 8000),
                      convert(varchar(8000), Substring(Content, 32001, 8000),
                      ...

                      dbo.tblIssues.SiteID, dbo.tblIssues.Name, dbo.tblIssues.IssueID, dbo.tblArticles.Title, dbo.tblAuthors.Name,tblArticlePages_1.PageNum,
                      dbo.vwArticles_AllArticles.ContextType, dbo.vwArticles_AllArticles.ContextSubtype, dbo.tblArticlePages.Content
HAVING      (dbo.tblIssues.SiteID = 1)
ORDER BY dbo.tblIssues.IssueID

Very messy, indeed.
0
 

Author Comment

by:tech_question
Comment Utility

Perkins I did use the above query although for 7000. I get  the following error:
Cannot sort a row of size 10256, which is greater than the allowable maximum of 8094.



SELECT     TOP 100 PERCENT dbo.tblIssues.Name AS [Issue Date], dbo.vwArticles_AllArticles.ContextType, dbo.vwArticles_AllArticles.ContextSubtype,
                      dbo.tblAuthors.Name AS Author, dbo.tblArticles.Title, tblArticlePages_1.PageNum,
convert(varchar(7000), Substring(tblArticlePages_1.content, 0, 7000))
               

FROM         dbo.tblArticleAuthors INNER JOIN
                      dbo.tblAuthors ON dbo.tblArticleAuthors.AuthorID = dbo.tblAuthors.AuthorID INNER JOIN
                      dbo.tblArticles ON dbo.tblArticleAuthors.ArticleID = dbo.tblArticles.ArticleID INNER JOIN
                      dbo.tblContextSubtypes ON dbo.tblAuthors.ContextSubtypeID = dbo.tblContextSubtypes.ContextSubtypeID INNER JOIN
                      dbo.tblIssues ON dbo.tblArticles.IssueID = dbo.tblIssues.IssueID INNER JOIN
                      dbo.vwArticles_AllArticles ON dbo.tblArticles.ArticleID = dbo.vwArticles_AllArticles.ArticleID INNER JOIN
                      dbo.tblArticlePages tblArticlePages_1 ON dbo.tblArticles.ArticleID = tblArticlePages_1.ArticleID CROSS JOIN
                      dbo.tblArticlePages
GROUP BY dbo.tblIssues.SiteID, dbo.tblIssues.Name, dbo.tblIssues.IssueID, dbo.tblArticles.Title, dbo.tblAuthors.Name, tblArticlePages_1.PageNum,
                      dbo.vwArticles_AllArticles.ContextType, dbo.vwArticles_AllArticles.ContextSubtype,
                  convert(varchar(7000), Substring(tblArticlePages_1.content, 0, 7000)),
                  convert(varchar(7000), Substring(tblArticlePages_1.content, 7001, 7000)),
                  convert(varchar(7000), Substring(tblArticlePages_1.content, 14001, 7000))


                 
HAVING      (dbo.tblIssues.SiteID = 1)
ORDER BY dbo.tblIssues.IssueID
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Plus, you would have to add the substring converts in your select statement too (not for the group by to work, but to get the full text field)....
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
"Perkins I did use the above query although for 7000. I get  the following error:
Cannot sort a row of size 10256, which is greater than the allowable maximum of 8094."


That's the next limit....Although, books online says the limit for an ORDER BY clause is  8060bytes...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
arbert,

Thanks for keeping me honest.  I learn something everyday.
0
 

Author Comment

by:tech_question
Comment Utility
I did use it in the select statement too. I get the following error:
Server: Msg 8648, Level 17, State 95, Line 1
Could not insert a row larger than the page size into a hash table. Resubmit the query with the ROBUST PLAN hint.


SELECT     TOP 100 PERCENT dbo.tblIssues.Name AS [Issue Date], dbo.vwArticles_AllArticles.ContextType, dbo.vwArticles_AllArticles.ContextSubtype,
                      dbo.tblAuthors.Name AS Author, dbo.tblArticles.Title, tblArticlePages_1.PageNum,
      convert(varchar(7000), Substring(tblArticlePages_1.content, 0, 7000)),
       convert(varchar(7000), Substring(tblArticlePages_1.content, 7001, 7000)),
                  convert(varchar(7000), Substring(tblArticlePages_1.content, 14001, 7000))
               

FROM         dbo.tblArticleAuthors INNER JOIN
                      dbo.tblAuthors ON dbo.tblArticleAuthors.AuthorID = dbo.tblAuthors.AuthorID INNER JOIN
                      dbo.tblArticles ON dbo.tblArticleAuthors.ArticleID = dbo.tblArticles.ArticleID INNER JOIN
                      dbo.tblContextSubtypes ON dbo.tblAuthors.ContextSubtypeID = dbo.tblContextSubtypes.ContextSubtypeID INNER JOIN
                      dbo.tblIssues ON dbo.tblArticles.IssueID = dbo.tblIssues.IssueID INNER JOIN
                      dbo.vwArticles_AllArticles ON dbo.tblArticles.ArticleID = dbo.vwArticles_AllArticles.ArticleID INNER JOIN
                      dbo.tblArticlePages tblArticlePages_1 ON dbo.tblArticles.ArticleID = tblArticlePages_1.ArticleID CROSS JOIN
                      dbo.tblArticlePages
            
GROUP BY dbo.tblIssues.SiteID, dbo.tblIssues.Name, dbo.tblIssues.IssueID, dbo.tblArticles.Title, dbo.tblAuthors.Name, tblArticlePages_1.PageNum,
                      dbo.vwArticles_AllArticles.ContextType, dbo.vwArticles_AllArticles.ContextSubtype,
                  convert(varchar(7000), Substring(tblArticlePages_1.content, 0, 7000)),
                  convert(varchar(7000), Substring(tblArticlePages_1.content, 7001, 7000)),
                  convert(varchar(7000), Substring(tblArticlePages_1.content, 14001, 7000))
                 
HAVING      (dbo.tblIssues.SiteID = 1)

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 34

Expert Comment

by:arbert
Comment Utility
Just another reason why text columns suck!!!  Can't wait for Yukon....

Try this and see if it make a difference--I still think you're going to bump up against the sort limit...

SELECT     TOP 100 PERCENT dbo.tblIssues.Name AS [Issue Date], dbo.vwArticles_AllArticles.ContextType, dbo.vwArticles_AllArticles.ContextSubtype,
                      dbo.tblAuthors.Name AS Author, dbo.tblArticles.Title, tblArticlePages_1.PageNum,
      convert(varchar(7000), Substring(tblArticlePages_1.content, 0, 7000)),
      convert(varchar(7000), Substring(tblArticlePages_1.content, 7001, 7000)),
                convert(varchar(7000), Substring(tblArticlePages_1.content, 14001, 7000))
             

FROM         dbo.tblArticleAuthors INNER JOIN
                      dbo.tblAuthors ON dbo.tblArticleAuthors.AuthorID = dbo.tblAuthors.AuthorID INNER JOIN
                      dbo.tblArticles ON dbo.tblArticleAuthors.ArticleID = dbo.tblArticles.ArticleID INNER JOIN
                      dbo.tblContextSubtypes ON dbo.tblAuthors.ContextSubtypeID = dbo.tblContextSubtypes.ContextSubtypeID INNER JOIN
                      dbo.tblIssues ON dbo.tblArticles.IssueID = dbo.tblIssues.IssueID INNER JOIN
                      dbo.vwArticles_AllArticles ON dbo.tblArticles.ArticleID = dbo.vwArticles_AllArticles.ArticleID INNER JOIN
                      dbo.tblArticlePages tblArticlePages_1 ON dbo.tblArticles.ArticleID = tblArticlePages_1.ArticleID CROSS JOIN
                      dbo.tblArticlePages
         
GROUP BY dbo.tblIssues.SiteID, dbo.tblIssues.Name, dbo.tblIssues.IssueID, dbo.tblArticles.Title, dbo.tblAuthors.Name, tblArticlePages_1.PageNum,
                      dbo.vwArticles_AllArticles.ContextType, dbo.vwArticles_AllArticles.ContextSubtype,
                convert(varchar(7000), Substring(tblArticlePages_1.content, 0, 7000)),
                convert(varchar(7000), Substring(tblArticlePages_1.content, 7001, 7000)),
                convert(varchar(7000), Substring(tblArticlePages_1.content, 14001, 7000))
               
HAVING      (dbo.tblIssues.SiteID = 1)
option(robust plan)
0
 

Author Comment

by:tech_question
Comment Utility
This is the error I get now.
The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.  Resubmit your query without the ROBUST PLAN hint.

0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Well, you're over the limit of the internal for the sort--not much you can do using the above scenario
0
 

Author Comment

by:tech_question
Comment Utility
These are my table structures . Could you please suggest me an alternative:

tblArticles: ArticleID,Title,Summary,ContextSubTypeID,PostDate
tblArticlePages: ArticlePageID,ArticleID,PageNum,Content
tblAuthors:AuthorID, Name
tblArticleAuthors:ArticleID,AuthorID
tblContextSubtype:ContextSubTypeID,Name


I need to get  Author,PostDate,Title,tblContextSubType.Name,PageNum,Content,  - these have to grouped accordingly by the respective fields. Content need not be grouped by.
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
So all these fields together aren't unique Author,PostDate,Title,tblContextSubType.Name,PageNum and you do have to group by?
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
0
 

Author Comment

by:tech_question
Comment Utility
that is right  Arbert
0
 
LVL 5

Expert Comment

by:a1x
Comment Utility
arbert, you can add content to the list of fields that aren't unique (causing the problem), all of which seems rather unlikely.  

Based on the schema given, the solution would seem to be a very simple query joining all the given tables.  (tblIssues is missing)

0
 

Author Comment

by:tech_question
Comment Utility
I have written an ASP program (two loops) to solve this.! Thanks for all your posts. Could someone please close this question. !
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>Could someone please close this question. !<<
That would be you.  See here:
I answered my question myself. What do I do?
http://www.experts-exchange.com/help.jsp#hi70
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Do you need some help reading the link I posted?  Here is what it says:
<quote>
I answered my question myself. What do I do?
 
Post a question in the Community Support topic area asking for a refund, and asking the Moderators to close the question. You'll be required to post your solution in your original question. A Moderator will post a notice of your request which will give the participants 96 hours to object to the refund. Note that if it resembles one of the suggested comments, the likelihood is that your request will not be granted, but rather, the points will be awarded to the Expert who makes the suggestion. In your Community Support request, remember to post a link to the original question.
 
</quote>
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
Comment Utility
PAQd with 500 points refunded

GhostMod
CS Moderator
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server Log File Space 6 33
C# SQL BULK INSERT CLASS 5 33
Distinct values from two tables 14 16
Convert int to military time 8 20
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now