How to Retrieve the First and Last Records from Grouped Rows with Aggregate Functions in MySQL?

Retrieving First and Last Records from Grouped Rows with Aggregate Functions in MySQL

In SQL, aggregate functions allow for efficient summarization of data across groups. However, when encountering grouped records, users may encounter challenges in retrieving the first and last records of each group. This becomes particularly pertinent when working with large tables.

Consider the following query:

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

While this query provides aggregate statistics for each group (such as minimum and maximum prices), it does not provide the first and last records for each week. To address this need, MySQL offers several approaches.

Using GROUP_CONCAT and SUBSTRING_INDEX

One efficient technique involves leveraging the GROUP_CONCAT and SUBSTRING_INDEX functions. GROUP_CONCAT concatenates specified values within a group, separated by a delimiter. SUBSTRING_INDEX, in turn, extracts substrings from concatenated strings.

Here's how we can utilize these functions to achieve our desired results:

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 approach eliminates the need for costly subqueries and proves highly efficient for such specific scenarios.

For further insights into the GROUP_CONCAT and SUBSTRING_INDEX functions, refer to the official MySQL documentation.

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