Re: data type error in my query

From: Steve Kass (skass_at_drew.edu)
Date: 09/17/04 

Date: Fri, 17 Sep 2004 18:17:52 -0400

D,

  If blogID is a unique identifier for rows of tblWeblog, you can do this
(and it will also prevent the off chance that two blogIDs match on the
header, comment, and postTime columns). If not, replace blogID here with
the column or columns that uniquely identify a tblWeblog row.

select top 5 W2.blogDate, W2.blogHeader, W2.blogComment, W2.blogPostTime,
G.Comments
from (
  select W1.blogID, count(*) as Comments
  from dbo.tblWeblog as W1
  left outer join dbo.tblUserComments as C
  on W1.blogID = C.blogID
  group by W1.blogID
) G join tblWeblog as W2
on G.blogID = W2.blogID
order by W2.blogDate desc

[untested]

Steve Kass
Drew University

"-D-" <noone@nospam.com> wrote in message
news:ey3JMDQnEHA.2764@TK2MSFTNGP10.phx.gbl...
>I have the following query:
>
> SELECT TOP 5 W.blogDate, W.blogHeader, W.blogComment, W.blogPostTime,
> COUNT(C.blogID) AS Comments
> FROM dbo.tblWeblog AS W LEFT OUTER JOIN dbo.tblUserComments AS C ON
> W.blogID = C.blogID
> GROUP BY W.blogDate, W.blogHeader, W.blogComment, W.blogPostTime
> ORDER BY W.blogDate DESC
>
> I receive the error:
> "The text, ntext, and image data types cannot be compared or sorted,
> except
> when using IS NULL or LIKE"
>
> After troubleshooting the error I discovered that GROUP BY doesn't allow
> the
> field blogComment because it uses a text data type, so that is why the
> error
> is thrown. If I strip out W.blogComment the query runs fine. Problem is,
> I
> need to include the blogComment field in the resultset.
>
> So, how can I correct the above query to allow the field, W.blogComment,
> within the statement and return its value in the resultset?
>
> Any help is greatly appreciated. Thanks.
> -D-
>
>