[thelist] SQL query help
Chris Spruck
cspruck at mindspring.com
Tue Jan 1 23:41:07 CST 2002
Forward from Rudy <r937 at interlog.com>, who's having posting trouble.
At 06:46 PM 1/1/2002, Erik Mattheis wrote:
>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.
hi erik
first, think of "the most recent active poll" as "the active poll with the
date equal to the highest date of all the active polls"
select poll_id
from polls
where poll_active=0
and poll_date =
( select max(poll_date)
from polls
where poll_active=0 )
yeah, it's ugly, but there you go, you have to do it that way
that'll be part of the final query (although it'll run nicely by iteself)
now count the options for this poll
select option_id, count(*)
from poll_answers
where poll_id = ( see above )
group by option_id
order by 2 desc
note that "2 desc" refers to the second column in the result set, i.e. the
counts
now just join this to the poll_options table to get the option text, and
use TOP to restrict the result
select top 1 poll_options.option_id, count(*), poll_options.option_text
from poll_answers, poll_options
where poll_answers.poll_id = poll_options.poll_id
and poll_answers.poll_id =
( select poll_id
from polls
where poll_active=0
and poll_date =
( select max(poll_date)
from polls
where poll_active=0 ) )
group by poll_answers.option_id
order by 2 desc
CAUTION: ordinarily i would test this before putting it forth as a
potential solution
let me know if it doesn't work
you will hit syntax errors immediately, and semantic errors can be checked
by having a good sample set in your tables (i.e. if the query runs, you
still have to be able to check the results and convince yourself that they
are the right results)
rudy
More information about the thelist
mailing list