Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Unanswered: Basic Query: Alternatives to 'Group By' for nText column

    I am having some difficulty writing a relatively basic query. The objective is to retrieve the new stories (headlines) for the past 3 days from the database. Since each headline can be assigned multiple categories (topics) the query returns a row for every headline assignment. I can't use the 'Group By' expression because one of the columns is nText.

    So basically if there is an article written yesterday, "I Love Cats" that gets assigned both topics 'CATS' and 'PETS' I only it returned with the first topic assigned... 'CATS'. Here is a little image of the three tables being called:

    http://64.225.154.232/temp_dbDiagram.gif

    I don't think that this query is too difficult, but I'm just getting my feet wet with writing queries that are more than select * from whatever. Any insight or recommendations are greatly appreciated.

    Code:
    SELECT headline.HEADLINE_ID, headline.HEADLINE_TITLE, headline.HEADLINE_DATE, headline.HEADLINE_THUMBNAIL, 
        topic.TOPIC_NAME, topic.TOPIC_URL
    FROM tbl_CCF_Headlines headline INNER JOIN
        tbl_CCF_Headlines_Topics ON headline.HEADLINE_ID = tbl_CCF_Headlines_Topics.HEADLINE_ID INNER JOIN
        tbl_CCF_Topics topic ON tbl_CCF_Headlines_Topics.TOPIC_ID = topic.TOPIC_ID
    WHERE (headline.HEADLINE_DATE IN
          (SELECT     TOP 3 HEADLINE_DATE
           FROM          tbl_CCF_HEADLINES
           GROUP BY HEADLINE_DATE
           ORDER BY HEADLINE_DATE DESC))
    ORDER BY headline.HEADLINE_DATE DESC

  2. #2
    Join Date
    Feb 2002
    Posts
    3
    Try to cast youe text column to varchar(1000).

  3. #3
    Join Date
    Mar 2002
    Location
    Finland
    Posts
    18
    What if the nText-field contains more than 1000 characters? What if it contains more than 4000 characters which I believe is the limit for nVarChar? I have this very same situation and am yet to find a solution...

    -Tuukka
    Cipher
    If you reach for the stars, you might not quite catch one but you won't get a handful of mud, either.

Posting Permissions

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