[thelist] SQL query help

[Erik Mattheis]

><tip type="data modelling">
>never let the avoidance of sql complexity drive your table design
></tip>
>
>i was biting my tongue trying not to comment on your poll design, as i,
>too, could see several ways to improve it

I'm all ears ... I know there's ways I could be doing things better 
in the SQL/DB arena.

>the sql i gave you was horribly complex (did it work, by the way?  it would
>be nice to know, in case i was wrong, because i like to put out little
>fires like that before they turn into big ones)

It gives the same error I was getting when trying to come up with 
something myself - it's the most dreaded error I get because I never 
understand why it happens:

[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.

Here is your query again:

    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

I can easily follow that except for the first and last lines:

What is the count(*) counting? I'm looking for the top 
Count(poll_answers.option_id)
The way it is here, what value will determine what the TOP result is?

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


>but like my tip says, sql complexity avoidance is not the main design
>criterion

This is my thinking ...I'm much more interested in keeping page 
processing time down than keeping database size down - I've got tons 
of disk space. And don't nested SELECTS like this significantly slow 
down processing time? It seems a better option for the purpose at 
hand to duplicate stuff if it will speed up page processing.

>if you are looking to restrict duplicates (e.g. the same IP number)
>consider using a unique constraint, and let the database do it for you
>
>it would have to be a multi-column constraint (e.g. including poll number),
>so make sure your database can handle that (sql/server can)

I can see that - but it would have to look at three things: poll_id, 
the user's IP and the date. And again, page processing speed is the 
most important thing other than making it slightly difficult to vote 
in the same poll more than once an hour.







-- 

__________________________________________
- Erik Mattheis

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

__________________________________________