I am trying to do fectch the first and the last record of a 'grouped' record. More precisely, I am doing a query like this
SELECT MIN(low_price), MAX(high_price), open, close
WHERE date BETWEEN(.. ..)
GROUP BY YEARWEEK(date)
You want to use
SUBSTRING_INDEX( GROUP_CONCAT(CAST(open AS CHAR) ORDER BY datetime), ',', 1 ) AS open SUBSTRING_INDEX( GROUP_CONCAT(CAST(close AS CHAR) ORDER BY datetime DESC), ',', 1 ) AS close
This avoids expensive sub queries and I find it generally more efficient for this particular problem.
Check out the manual pages for both functions to understand their arguments, or visit this article which includes an example of how to do timeframe conversion in MySQL for more explanations.