Get question with newest answer SQL Query

Question

Hi,

I have a model like:

Question - Id - CreatedOn

Answer - Id - CreatedOn - QuestionID

No I want to get the first question with the newest answer.

I am using SQL Server 2005.

Thanks, Chris

Answer 1

Something like this:

SELECT TOP 1 Question FROM Questions JOIN Answers ON Questions.Id=Answers.QuestionID ORDER BY Answers.CreatedOn DESC;

Answer 2

select top 1 Question from questions
  inner join answers on questions.Id = answers.QuestionId
order by answers.CreatedOn desc

Sorry for the delay in replying to the comment, I didn't have time earlier to test it out:

This gets more complicated, but something like this should work:

select top 5 Question from questions
  inner join answers on questions.Id = answers.QuestionId
GROUP BY Question
ORDER BY max(answers.CreatedOn) desc

This seems to be the standard pattern to effectively get a distinct set of data from a table without including the order by column in the record set.

Be aware that the number of elements you want to select (in this case 5) has to be a constant in SQL. If you have the advantage of using something like LINQ to SQL, you could write this in LINQ and have the take amount as a variable.

So, in response to the latest comment, you'd just add max(answers.CreatedOn) to the select statement:

select top 5 Question, max(answers.CreatedOn)
from questions
  inner join answers on questions.Id = answers.QuestionId
GROUP BY Question
ORDER BY max(answers.CreatedOn) desc

Answer 3

Select top 1 *
from questions, answers
where question.id = answers.questionid
order by answers.createdon desc

Answer 4

I would try something like this

SELECT Id, CreatedOn    
FROM Question Q    
WHERE Q.id IN    
(SELECT DISTINCT TOP 5 A.QuestionID FROM Answer A ORDER BY A.CreatedOn)