Solved

how to put a long text in a Group by clause

Posted on 2004-04-15
24
292 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
ID: 10835478
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
ID: 10835522
I would need all the characters. What isthe alternative?
0
 
LVL 34

Expert Comment

by:arbert
ID: 10835549
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Expert Comment

by:pwang1973
ID: 10835736
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
ID: 10836061
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
ID: 10836211
>>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
ID: 10836393

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
ID: 10836402
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
ID: 10836420
"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
ID: 10836602
arbert,

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

Author Comment

by:tech_question
ID: 10836604
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
 
LVL 34

Expert Comment

by:arbert
ID: 10836712
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
ID: 10837360
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
ID: 10837371
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
ID: 10837580
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
ID: 10837753
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
ID: 10837816
0
 

Author Comment

by:tech_question
ID: 10838342
that is right  Arbert
0
 
LVL 5

Expert Comment

by:a1x
ID: 10839631
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
ID: 11484579
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
ID: 11484804
>>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
ID: 11498526
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
ID: 11579337
PAQd with 500 points refunded

GhostMod
CS Moderator
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

789 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