How do I get the top 1 of a linked table?

Ok, so say I have two tables.

Question

• questionID
• QuestionDescription

Answer

• AnswerID
• QuestionID
• AnswerDescription
• CreatedDate

And say I want to create a query which returns the question and latest created answer like these columns

QuestionID QuestionName AnswerDescription CreatedDate

Is that possible?

I can do a query that gets all the combinations of quesitons and answers

SELECT q.QuestionID, q.QuestionName, a.Ans开发者_运维技巧werDescription, a.CreatedDate FROM QUESTION q
INNER JOIN ANSWER a
ON q.questionID = a.QuestionID

but is there a way I can do something like this but have it only return the latest answer instead of all of them?

---

To get per question, SQL Server 2005+

SELECT
   q.QuestionID, q.QuestionName,
   a.AnswerDescription, a.CreatedDate
FROM
    QUESTION q
    CROSS APPLY
    (
     SELECT TOP 1 a2.AnswerDescription, a2.CreatedDate
     FROM ANSWER a2
     WHERE q.questionID = a2.QuestionID
     ORDER BY a2.CreatedDate DESC
    ) a

---

SELECT q.QuestionID, 
       q.QuestionName, 
       a.AnswerDescription, 
       a.CreatedDate
FROM QUESTION AS q
  INNER JOIN (SELECT QuestionID,
                     AnswerDescription,
                     CreatedDate,
                     ROW_NUMBER() OVER(PARTITION BY QuestionID 
                                       ORDER BY CreatedDate DESC) AS rn
              FROM ANSWER) AS a       
    ON q.questionID = a.QuestionID AND 
       a.rn = 1

---

SELECT top 1 q.QuestionID, q.QuestionName, a.AnswerDescription, a.CreatedDate FROM QUESTION q
INNER JOIN ANSWER a
ON q.questionID = a.QuestionID
ORDER BY a.CreatedDate DESC