Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2001
    Washington, DC

    Unanswered: Syntax reality check for joining + grouping results?

    I have two tables, STORIES and STORYTEXT. They're joined by a common "StoryID" field. There can be an unlimited number of STORYTEXT records that correspond to each STORIES record.

    I want to query these two tables based on certain criteria, and I only want one result per STORIES record. I can do that using MAX() as below:

    SELECT Stories.Headline, Stories.StoryID, MAX(StoryText.LastTimeModified)
    FROM Stories LEFT OUTER JOIN StoryText ON Stories.StoryID = StoryText.StoryID
    GROUP BY Stories.StoryID, Stories.Headline

    But as soon as I add STORYTEXT.WordCount to the SELECT clause, I'm back to getting multiple results for each STORIES record (one for each unique WordCount value).

    I'm unclear why this is happening, but I've hacked together a query using a subselect that *seems* to return one record for each STORIES entry, including the WordCount for only the STORYTEXT record with the max LastTimeModified value. That query is below. If anyone can take a look at it and tell me if the query makes sense, I'd greatly appreciate it, because I'm (still!) hazy on subselects so it's possible my new query is all wrong. Thanks in advance.

    SELECT s.Headline, s.StoryID, MAX(x.LastTimeModified) AS LastTimeModified
    FROM Stories s INNER JOIN
    (SELECT Stories.Headline, Stories.StoryID, MAX(StoryText.LastTimeModified) AS LastTimeModified, StoryText.WordCount AS WordCount
    FROM Stories LEFT OUTER JOIN StoryText ON Stories.StoryID = StoryText.StoryID
    GROUP BY Stories.StoryID, Stories.Headline, Stories.StoryID, StoryText.WordCount) x ON s.StoryID = x.StoryID
    GROUP BY s.Headline, s.StoryID

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    i'd be interested in seeing what your plan analyzer gives as the relative cost of your execution plan versus the following --
    SELECT s.Headline, s.StoryID
         , t.LastTimeModified
         , t.WordCount
      FROM Stories s
      JOIN StoryText t
        ON s.StoryID = t.StoryID
     WHERE t.LastTimeModified
         = (select max(LastTimeModified)
              from StoryText
             where StoryID = t.StoryID)
    your query seems unnecessarily convoluted

    mine's a simple join with a correlated subquery

    if you wanted to include stories that don't have a matching storytext row at all, that's a slightly different query


Posting Permissions

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