Complex SQL Query
July 19, 2008 1:49 PM   Subscribe

SQLFilter: I'm struggling with a specific SQL query. I have 2 tables, 'questions' and 'answers', which are linked by a field in answers. I would like to select the questions which have the most answers in the last 24 hours.

I have wo tables:

'questions' with columns 'id' and other irrelevant fields
'answers' with columns 'id', 'ofquestion', 'date', and other irrelevant fields. The 'ofquestion' field is the ID of the question row that it is associated with.

Each question can be answered multiple times, just as here on the green. I am trying to formulate an SQL query that will select the 5 questions with the most answers in the last 24 hours.

This is the closest that I've been able to come, though it still doesn't work.
SELECT * FROM questions WHERE exists (
	SELECT answers.ofquestion, count(*) FROM answers WHERE answers.ofquestion = answers.ofquestion
	AND answers.id <> answers.id
	AND answers.date > SUBTIME( NOW( ) , '24:0:0.000000' )
	AND answers.ofquestion = questions.id
	GROUP BY answers.ofquestion
	ORDER BY count(*)
)
I'm no SQL expert, so this may not even be close. Anybody have any suggestions? I'm using PHP, so I have that set of tools in my disposal if need be.

Thanks!
posted by charmston to Computers & Internet (2 answers total) 4 users marked this as a favorite
 
select * from (
SELECT count(*) c, answers.id from answers where answers.date > SUBTIME( NOW( ) , '24:0:0.000000' ) group by answers.id order by c desc limit 5
)
left join questions
on questions.id = answers.id;

The syntax of those aliases and the LIMIT may vary depending on your SQL implementation. I think the above works for MySQL and PostgreSQL at least.
posted by cmiller at 2:14 PM on July 19, 2008


Brilliant idea to use a derived table. I have heard of them, but never needed to use one. For posterity, here is the final query that I used:
select * from (
	SELECT count(*) num, answers.id FROM answers
	WHERE answers.ofquestion > 0
	GROUP BY answers.ofquestion
	ORDER BY num DESC 
	LIMIT 5
)
AS x LEFT JOIN questions ON questions.id = x.id

posted by charmston at 2:47 PM on July 19, 2008


« Older How safe is Firefox 2.0?   |   Wheel = Fail Newer »
This thread is closed to new comments.