maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

I think time is a separate field he orders on, he just mistyped the query.

Sent from my iPhone

> On Oct 4, 2015, at 1:20 AM, Pantelis Theodosiou <ypercube@xxxxxxxxx> wrote:
> 
> Did you mean to write 
> 
> ORDER BY value ASC   and   ORDER BY value DESC
> 
> in the subqueries? The "ORDER BY date" doesn't make sense in the subqueries as all the rows will have the same date, due to the "WHERE date=a.date" correlation.
> 
> Pantelis
> 
> 
>> On Sat, Oct 3, 2015 at 2:12 AM, Roberto Spadim <roberto@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp