[thelist] SQL query help

[Erik Mattheis]

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

Polls:
poll_id, poll_question, poll_date, poll_active

poll_options:
option_id, poll_id, option_text

poll_answers:
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 
votes?
-- 

__________________________________________
- Erik Mattheis

(612) 377 2272
http://goZz.com/

__________________________________________