maria-discuss team mailing list archive

[Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>]

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.