Hi guys
I have a problem and a possible solution, that i think is relative easy to develop (i didn't tested but i think the source is easy to change)

i have some queries like:


SELECT 
date,
(SELECT value FROM some_table WHERE date=a.date ORDER BY date ASC LIMIT 1) AS first,
(SELECT value FROM some_table WHERE date=a.date ORDER BY date DESC LIMIT 1) AS last
FROM some_table AS a
GROUP BY date


i want to "convert" the complex sub query, to a agregate function, and i thinking about group concat:
from mysql docs:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

we have ORDER BY
if we could include a "LIMIT" clause, i could rewrite this query to:

SELECT 
date,
GROUP_CONCAT(value ORDER BY date ASC LIMIT 1) AS first,
GROUP_CONCAT(value ORDER BY date DESC LIMIT 1) AS last
FROM some_table AS a
GROUP BY date


i know that i could have stats tables / materialized views (with flexview, i already tested :) ), but i want something more "easy to use" and not "very fast", just a "feature" to solve small problems


it's a nice idea? does anyone have this "problem" and solve with other solutions?

--
Roberto Spadim