nice :) how it's "rewrite"? 2015-10-02 22:25 GMT-03:00 Justin Swanhart <greenlion@gmail.com>:
Hi,
You could use Shard-Query. You don't need to shard the database, you can point shard-query at one database. It supports WINDOW functions.
select date, FIRST_VALUE(value) OVER (ORDER BY value ASC), LAST_VALUE(value) OVER (ORDER BY value ASC) FROM some_table AS a GROUP BY date
--Justin
On Fri, Oct 2, 2015 at 6:12 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle