Geeks With Blogs
AzamSharp Some day I will know everything. I hope that day never comes.

If you try to use the GROUP BY clause with the Text datatype it will throw an error telling you that you cannot use GROUP BY with Text DataType. There is a easy work around to this simply convert the Text Datatype to a varchar and the GROUP BY will work.

Here is a simple example:

-- Get all the post not depending if it has the thread or not 

SELECT p.PostID, p.Url, p.Title,COUNT(t.ThreadID),p.DateCreated,CONVERT(VARCHAR(8000),p.Description)
"Comments" FROM Posts p
LEFT OUTER JOIN Threads t ON p.PostID = t.PostID 
p.Active = 1 
CONVERT(CHAR(6), p.DateCreated, 112) = CONVERT(CHAR(6),GETDATE(), 112) 
GROUP BY p.PostID,p.Url,p.Title,p.DateCreated,CONVERT(VARCHAR(8000),p.Description)

powered by IMHO


Posted on Monday, December 12, 2005 12:09 AM | Back to top

Comments on this post: Using GROUP BY WITH Text DataType

# re: Using GROUP BY WITH Text DataType
Requesting Gravatar...
Great Trick..!
Left by Puma on Apr 10, 2008 9:21 AM

Your comment:
 (will show your gravatar)

Copyright © Mohammad Azam | Powered by: