[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