mysql: select between

Question

I am getting invalid use of group function, not really sure where the problem is

expected result is list of timestamps from within xxx seconds starting from the max available

Please advise.

SELECT timestamp, response_time 
FROM results
WHERE id = XYZ AND timestamp between 
(SELECT MAX(timestamp) FROM results inn WHERE id = 22) AND 
(SELECT timestamp FROM results WHERE id =22 AND timestamp = MAX(timestamp) - XXX)
ORDER BY timestamp DESC

thank you

Answer 1

SELECT  timestamp, response_time
FROM    results
WHERE   id = @xyz
        AND timestamp BETWEEN
        ( 
        SELECT  MAX(timestamp)
        FROM    results
        WHERE   id = 22
        ) AND
        ( 
        SELECT  MAX(timestamp)
        FROM    results
        WHERE   id = 22
        ) - @xxx
ORDER BY
        timestamp DESC

Make sure you have an index on (id, timestamp) for this to work fast.