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