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

Retrieving First and Last Records from Grouped Data with MySQL Aggregate Functions

When dealing with grouped data in MySQL, it may be necessary to access the first and last records within each group. For example, consider the following query:

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

This query retrieves the minimum low price, maximum high price, opening, and closing prices for each week of the year. However, if the goal is to obtain the first and last records of each group, the above query is insufficient.

Solution

To achieve this, one can utilize the GROUP_CONCAT and SUBSTRING_INDEX functions. The following query modifies the original query to include columns for the first and last opening prices:

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)

Explanation

• GROUP_CONCAT: Concatenates the values of the specified column (e.g., open) within each group, separated by a comma.
• SUBSTRING_INDEX: Extracts the substring before or after the specified occurrence of a delimiter (in this case, a comma).

By using these functions, the query efficiently retrieves the first opening price and the last closing price for each group. This approach avoids the need for multiple subqueries and offers a performant solution for retrieving the desired data.

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