Hi Brannon,
* Brannon King <[EMAIL PROTECTED]> [2006-05-23 05:35]:
* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-23 02:35]:
> >select qi, ri, drl, max(score), min(score) from ...
> >
> >What values of qi, ri, and drl would you want
> >this query to return?
> >
> >What you have to do is:
> >
> > SELECT qi, ri, drl, score
> > FROM ...
> > WHERE score=(SELECT max(score) FROM ...)
> >
> Thank you for the instruction, although the other query seemed
> to return the right results. (Not that I did any extensive
> testing....)
>
> So do I have to repeat all my constraints for both the main
> query and the subquery? Or is it even legal to specify a where
> clause when doing the max?
Hmm. I would normally suggest an inline view:
SELECT qi, ri, drl, score
FROM ( SELECT qi, ri, drl, score FROM [ rest of your original query here ]
) candidates
WHERE score = ( SELECT MAX( score ) FROM candidates );
Unfortunately SQLite doesn’t seem to capable of referring to
subqueries everywhere I’d expect to be able to – it complains
that it doesn’t know of a table `candidates`.
Using a named view will work:
CREATE VIEW candidates AS SELECT qi, ri, drl, score FROM [ rest of your
original query here ];
SELECT qi, ri, drl, score
FROM candidates
WHERE score = ( SELECT MAX( score ) FROM candidates );
However I have a hunch that it computes the result set for the
`WHERE` clause from scratch by redoing the entire query, instead
of using the already-computed result set from the `FROM` clause.
In that case you don’t gain any performance, “only” clarity.
Regards,
--
Aristotle Pagaltzis // <http://plasmasturm.org/>
