Brannon King <brannonking-/[EMAIL PROTECTED]>
wrote:
I'm wondering if the following query can be done as a single query
rather than running it in a (nested) loop.
Suppose a database with five columns; xStart, yStart, xEnd, yEnd,
score. I need the maximum score at the midpoint of the line quantized
to a 64x64 grid. Psuedo code:
query = prepare("
SELECT score FROM db WHERE
(xStart+xEnd)/2 >= :left AND
(xStart+xEnd)/2 < :right AND
(yStart+yEnd)/2 >= :top AND
(yStart+yEnd)/2 < :bottom
ORDER BY score DESC LIMIT 1
")
xJump = max(xEnd in db) / 64.0;
yJump = max(yEnd in db) / 64.0;
for(int x = 0; x < 64; x++){
for(int y = 0; y < 64; y++){
left = x*xJump, righ = (x+1)*xJump
top = y*yJump, bottom = (y+1)*yJump
bind(query, left, right, top, bottom)
ret = step(query)
output[x][y] = score or 0 if no ret
}
}
select
cast( (xStart+xEnd)/2/(select max(xEnd) from db)*64 as integer)
cellX,
cast( (yStart+yEnd)/2/(select max(yEnd) from db)*64 as integer)
cellY,
max(score)
from db
group by cellX, cellY
Igor Tandetnik
