SQL - I need to limit the rows returned from the second table?

I am using SQL Server 2008.

I have a Questions table and I have an Answers table. The Questions table contains QuestionID, Question, and Position (order). The Answers table contains Answers and AnswerID. Each question can be answered multiple times.

Our Advisors should see ONLY the most recent answer, which is why we sort by AnswerID DESC.

Our Admins need to be able to see the history of each answer. That's why we don't delete answers.

My query right now looks like this:

 SELECT   Q.*, A.*
 FROM     Questions Q 
          LEFT JOIN Answers A 
               ON Q.QuestionID = A.QuestionID
 WHERE      FranchisorID = 10   
 ORDER BY   Q.Position, A.AnswerID DESC

My resultset with the query above looks like this:

QuestionID - Question - AnswerID - Answer 开发者_如何学编程- 

 1 what is your fave color? 3 Blue
 1 what is your fave color? 2 Green
 1 what is your fave color? 1 Red
 4 What year is this? 5 2011
 4 What year is this? 4 2010

I need my resultset to look like this:

 1 what is your fave color? 3 Blue
 4 What year is this? 5 2011

Can you tweak my little query for me?

One option would be to

• add a ROW_NUMBER
• PARTITION it by QuestionID
• ORDER BY DESC on AnswerID to only return the largest AnswerID's
• return rows where rn = 1

SQL Statement

SELECT  *
FROM    (
            SELECT  Q.QuestionID
                    , Q.Question
                    , A.AnwerID
                    , A.Answer
                    , rn = ROW_NUMBER() OVER (PARTITION BY QuestionID ORDER BY AnswerID DESC)
            FROM    Questions Q 
                    LEFT JOIN Answers A ON Q.QuestionID = A.QuestionID
             WHERE  FranchisorID = 10
        ) r
WHERE   r.rn = 1                            

• Use a CTE to get the latest answer ID

• Use an INNER JOIN to the CTE to filter your results.

  WITH LatestAnswer AS
  (
      SELECT  QuestionID,
              MAX(AnswerID) AS LatestAnswerID
      FROM    Questions
      INNER JOIN Answers ON Answers.QuestionID = Questions.QuestionID
      GROUP BY QuestionID
  )
  
  SELECT      Q.*, A.*
  FROM        Questions Q 
  LEFT JOIN   Answers A ON Q.QuestionID = A.QuestionID
  INNER JOIN  LatestAnswer ON LatestAnswer.LatestAnswerID = Answers.AnswerID
  WHERE       FranchisorID = 10   
  ORDER BY    Q.Position, A.AnswerID DESC
  

 SELECT   Q.*, A.*
 FROM     Questions Q 
   LEFT JOIN Answers A 
   ON A.AnswerId in 
     (select top 1 a2.AnswerId from Answers a2 
      where a2.QuestionId=Q.QuestionId order by a2.AnswerId DESC)
 WHERE      FranchisorID = 10   
 ORDER BY   Q.Position