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?
Also, please help me understand how the indexes are used on it in that
situation as well.
So let's say I change my query. Coming from these tables:
CREATE TABLE bounds (bi INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT NULL,
bqi INTEGER, bri INTEGER, bqis INTEGER, bris INTEGER);
CREATE TABLE results (qi INTEGER, ri INTEGER, drl INTEGER, score
INTEGER, qis INTEGER, ris INTEGER);
Assume that qi and ri are only unique together. Same for bqi, bri. All
the others are never guaranteed to be unique.
What indexes will maximize the speed of this query?
"select count(*) from results where qi = 5604 OR ri = 5468;"
Assuming I do need to repeat the constraints, what indexes will maximize the
speed of this query?
"select qi, ri, drl, score from results, bounds where
score=(SELECT max(score) from results, bounds
where (qi = 5604 OR ri = 5468)
AND qi >= bqis AND qi <= 5604 AND ri >= bris AND ri <= 5468
AND bi = 1 and qis = bqis AND ris = bris)
AND (qi = 5604 OR ri = 5468)
AND qi >= bqis AND qi <= 5604 AND ri >= bris AND ri <= 5468
AND bi = 1 and qis = bqis AND ris = bris
;"
That's kind of ugly.
