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@gmail.com> 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@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


_______________________________________________
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