Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how to put a long text in a Group by clause

Posted on 2004-04-15
24
Medium Priority
?
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

610 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