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

You want to use GROUP_CONCAT and SUBSTRING_INDEX:

SUBSTRING_INDEX( GROUP_CONCAT(CAST(open AS CHAR) ORDER BY datetime), ',', 1 ) AS openSUBSTRING_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.

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)) ZGroup By YearWeek, Date

Here is a great specific solution to this specific problem:http://topwebguy.com/first-and-last-in-mysql-a-working-solution/It's almost as simple as using FIRST and LAST in MySQL.

I will include the code that actually provides the solution but you can look upi the whole text:

SELECTword ,  (SELECT a.ip_addr FROM article aWHERE a.word = article.wordORDER BY a.updated  LIMIT 1) AS first_ip,(SELECT a.ip_addr FROM article aWHERE a.word = article.wordORDER BY a.updated DESC LIMIT 1) AS last_ipFROM notfound GROUP BY word;