You could either do something like selecting the content as a substring
(which will work in group by) or you could skip the Group BY and run the
count as sub-query
SELECT
...
SUBSTRING( clubroom.cb_content, 0, 1000 ) AS cb_content
...
FROM
...
GROUP BY
...
SUBSTRING( clubroom.cb_content, 0, 1000 )
...
Or maybe...
SELECT
...
(
SELECT COUNT(*) FROM ....
)
FROM
...
WHERE
...
.......................
Ben Nadel
www.bennadel.com
Certified Advanced ColdFusion Developer
Need Help?
www.bennadel.com/ask-ben/
-----Original Message-----
From: Mike | NZSolutions Ltd [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 30, 2006 6:37 PM
To: CF-Talk
Subject: GROUP BY error
Hi guys,
I have the following query...
SELECT clubroom.cb_id, clubroom.cb_dCreated, clubroom.cb_dModified,
clubroom.cb_content, clubroom.cb_isArchive,
COUNT(clubroom_images.cbi_id) AS image_count FROM clubroom LEFT OUTER JOIN
clubroom_images ON clubroom.cb_id = clubroom_images.cb_id GROUP BY
clubroom.cb_id, clubroom.cb_dCreated, clubroom.cb_dModified,
clubroom.cb_content, clubroom.cb_isArchive ORDER BY clubroom.cb_dCreated
DESC
And get the following error...
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]The text, ntext, and image
data types cannot be compared or sorted, except when using IS NULL or LIKE
operator.
Now I have checked the elements and it appears that the error is relating to
the cb_content field which is a text field. When this is removed from the
query the query runs fine.
In order to use the COUNT function I need to have the GROUP BY ?? and I also
need the cb_content field.
Any help would be greatly appreciated by this SQL dummie!
Mike :o)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251552
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
