| 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
WHERE
p.Active = 1
AND
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 |
Print | posted @ Monday, December 12, 2005 12:09 AM