[thelist] SQL query help

Erik Mattheis gozz at gozz.com
Tue Jan 1 21:41:48 CST 2002

I'm trying to think of a way to do this and it's not immediately coming to me:

Three tables: polls, poll_options, poll_results

poll_id, poll_question, poll_date, poll_active

option_id, poll_id, option_text

poll_answer_id, poll_id, option_id

Each time a vote is cast, a row is added to the poll_answers table

I need to come up with a query that selects the option that got the 
most votes from the most recent poll where poll_active=0. In other 
words, the winner of last week's poll.

I got as far as:

SELECT TOP 1 polls.poll_date, polls.poll_question, poll_options.option_text
FROM polls, poll_options
WHERE polls.poll_active = 0 AND
	poll_options.poll_id = polls.poll_id
ORDER BY polls.poll_date DESC

(this is for SQL Server 7)

Can I add some SQL so it returns the option_text that got the most votes?

I could just grab a COUNT of poll_answers.poll_option_id and loop 
through it in ColdFusion and only output the row with the greatest 
number of votes ... but I'd rather do it in SQL ... or is there some 
SQL that would increment a field in poll_options each time someone 

- Erik Mattheis

(612) 377 2272


More information about the thelist mailing list