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 <[email protected]> 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 <[email protected]> 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 : [email protected] >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
