maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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