How to Retrieve the First and Last Records from Grouped Results in MySQL?

Retrieving the First and Last Records from Grouped Results in MySQL

In MySQL, fetching the first and last records of a grouped result set while applying aggregate functions can be challenging. Consider the following query:

SELECT MIN(low_price), MAX(high_price), open, close
FROM symbols
WHERE date BETWEEN (.. ..)
GROUP BY YEARWEEK(date)

This query groups the data by week and calculates the minimum low price, maximum high price, open, and close values for each week. However, it does not provide the first and last records within each group.

To achieve this, one approach is to use the GROUP_CONCAT and SUBSTRING_INDEX functions:

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 solution allows you to concatenate all the open and close values for each group, separated by commas, and then extract the first and last values using SUBSTRING_INDEX. This approach avoids expensive subqueries and can be more efficient for this specific problem.

Refer to the MySQL documentation for more information on GROUP_CONCAT and SUBSTRING_INDEX functions. Additionally, you can explore other options such as using window functions or user-defined functions, depending on the specific requirements of your query.

The above is the detailed content of How to Retrieve the First and Last Records from Grouped Results in MySQL?. For more information, please follow other related articles on the PHP Chinese website!