Retrieving First and Last Records of Grouped Data with Aggregate Functions in MySQL

In MySQL, when fetching data from grouped records using aggregate functions, it can be challenging to retrieve the first and last records of the group. Although multiple queries can accomplish this, it can be inefficient for large tables.

To optimize this process, MySQL offers a more efficient solution using the GROUP_CONCAT and SUBSTRING_INDEX functions.

Using GROUP_CONCAT and SUBSTRING_INDEX

By using these functions together, you can concatenate values from the group and then extract the first and last values using SUBSTRING_INDEX. Here's how:

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

How it Works:

• GROUP_CONCAT concatenates all the open or close values into a single string, sorted by the datetime column.
• SUBSTRING_INDEX extracts the first and last values from the concatenated string by splitting it at the comma (','). The '1' argument indicates the first or last occurrence.

Example:

Consider the following query:

SELECT MIN(low_price), MAX(high_price),
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
FROM symbols
WHERE date BETWEEN(.. ..)
GROUP BY YEARWEEK(date)

This query efficiently retrieves the first (open) and last (close) records for each group, minimizing processing time for large tables.