Cant order by text values?

Hi,

I've got the following code that pulls out forum topics that have been added to on a particular day:

SELECT
forum_posts.post_date
, forum_threads.id
, forum_threads.subject
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
WHERE day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
GROUP BY forum_threads.id
,forum_threads.subject
,forum_posts.post_date
ORDER BY forum_threads.id ASC, forum_posts.post_date ASC

I tried including the body of the post in this:

SELECT
forum_posts.post_date
, forum_posts.body
, forum_threads.id
, forum_threads.subject
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
WHERE day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
GROUP BY forum_threads.id
,forum_threads.subject
,forum_posts.post_date
,forum_posts.body
ORDER BY forum_threads.id ASC, forum_posts.post_date ASC

But was told that "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

Well, the body field is text datatype alright, but how do I therefore include it in the results?The GROUP BY is what it is complaining about. Why is that there?

Originally posted by Spudhead
Hi,

I've got the following code that pulls out forum topics that have been added to on a particular day:

SELECT
forum_posts.post_date
, forum_threads.id
, forum_threads.subject
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
WHERE day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
GROUP BY forum_threads.id
,forum_threads.subject
,forum_posts.post_date
ORDER BY forum_threads.id ASC, forum_posts.post_date ASC

I tried including the body of the post in this:

SELECT
forum_posts.post_date
, forum_posts.body
, forum_threads.id
, forum_threads.subject
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
WHERE day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
GROUP BY forum_threads.id
,forum_threads.subject
,forum_posts.post_date
,forum_posts.body
ORDER BY forum_threads.id ASC, forum_posts.post_date ASC

But was told that "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

Well, the body field is text datatype alright, but how do I therefore include it in the results?|||Because I'm a muppet.

Thanks :)|||Make this...

SELECT
forum_threads.id
,forum_threads.subject
,forum_posts.post_date
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
GROUP BY forum_threads.id
,forum_threads.subject
,forum_posts.post_date
HAVING day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
ORDER BY forum_threads.id, forum_posts.post_date

...the option ASC is default;
...use the command Having.