[thelist] SQL query help

[Michele Foster]

[Forwarded message from Rudy - r937 at interlog.com]




>[Microsoft][ODBC SQL Server Driver][SQL Server] Column
>'poll_options.option_id' is invalid in the select list because it is
>not contained in either an aggregate function or the GROUP BY clause.

yeah, I'm usually good for a couple syntax errors before my queries will
run

try this --

    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_options.option_id, poll_options.option_text
      order by 2 desc

> What is the count(*) counting?

rows in each group that it collapses into one

each option_id/option_text combination is a group and has one row
in the result set

>The way it is here, what value will determine what the TOP result is?

the sequence of rows in the result set

>The "order by 2 desc" - what is this doing?

sequencing the rows into descending order by 2nd column, i.e. the count

> And don't nested SELECTS like this significantly slow
> down processing time?

not necessarily, assuming nested selects are even possible

(i think mysql has finally added support for subselects -- can anyone
confirm?)

> It seems a better option for the purpose at
> hand to duplicate stuff if it will speed up page processing.

that's a myth, sir

trading redundancy for processing speed is a
http://www.m-w.com/cgi-bin/dictionary?mug's+game

there's nothing wrong with having blazing speed as a design objective, but
it is only one system factor and must be balanced against other worthwhile
qualities like understandability, portability, and maintainability

I've seen many systems that were tuned and tweaked for performance by
careful, well-intentioned programmers, systems which ran real nice, until,
one day, something happened that required them to have to be changed, and
lo and behold, the original author was no longer with us, and the guy
taking over the system was standing firm on his estimate of two weeks to
make a change that the original author could have made in a few minutes,
using phrases like "bag of snakes," "house of cards," and "builtin job
security" to describe the despair with which he faced the task of opening
up the code...

rudy