Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Can't 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?

  2. #2
    Join Date
    Sep 2002
    Location
    Atlanta, GA USA
    Posts
    19

    Arrow Re: Can't order by text values?

    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?
    HueyStLoui

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Because I'm a muppet.


    Thanks

  4. #4
    Join Date
    Oct 2002
    Location
    São Paulo - Brasil
    Posts
    11

    Re: Can't order by text values?

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •