How to fetch the first and last record of a grouped record in a MySQL query with aggregate functions?

Question

I am trying to fetch 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 FROM symbols WHERE date BETWEEN(.. ..) GROUP BY YEARWEEK(date) 

but I'd like to get the first and the last record of the group. It could by done by doing tons of requests but I have a quite large table.

Is there a (low processing time if possible) way to do this with MySQL?

Answer 1

You want to use GROUP_CONCAT and SUBSTRING_INDEX:

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.

Answer 2

Try This to start with... :

Select YearWeek, Date, Min(Low_Price), Max(High_Price) From    (Select YEARWEEK(date) YearWeek, Date, LowPrice, High_Price     From Symbols S     Where Date BETWEEN(.. ..)     GROUP BY YEARWEEK(date)) Z Group By YearWeek, Date