maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Hi,

Try select max(total) from (select mail_out_anon + mail_out_auth as total
... ) dependent_subquery;

On Mon, Jun 22, 2015 at 9:05 AM, Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>
wrote:

> Benoit Panizzon <benoit.panizzon@xxxxxx> writes:
>
> > MariaDB [maildb]> select mail_out_anon+mail_out_auth,timeslice from
> > domaincounters where domain_id=19 order by timeslice desc limit 24;
>
> This takes the first 24 rows by timeslice, and selects them.
>
> > select max(mail_out_anon+mail_out_auth),timeslice from domaincounters
> where
> > domain_id=19 order by timeslice desc limit 24;
>
> This takes a _single_ row containing the max(mail_out_anon+mail_out_auth)
> over the entire table. Then it sorts the single row, and limits it to 24
> rows (neither of which does anything of course).
>
> > +----------------------------------+---------------------+
> > | max(mail_out_anon+mail_out_auth) | timeslice           |
> > +----------------------------------+---------------------+
> > |                              656 | 2015-06-15 13:00:00 |
> > +----------------------------------+---------------------+
> >
> > Nope, wrong value...
>
> Probably just the maximum over the entire table, right?
>
> Maybe try something like this (untested):
>
> SELECT *
>   FROM (SELECT mail_out_anon+mail_out_auth ss, timeslice
>           FROM domaincounters
>          WHERE domain_id=19
>          ORDER BY timeslice desc
>          LIMIT 24) tmp
>  ORDER BY ss DESC
>  LIMIT 1;
>
> > Hmm. I think I see the problem now... the MAX() is done before sorting
> and
> > limiting the result, right?
>
> Right.
>
>  - Kristian.
>
> _______________________________________________
> 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
>