mysql `having` operation with specific @variable value

Question

I give rank to each row by its create_time in table instances group by (task_id, task_time) as primary key.I My SQL is as following

 SELECT
 task_id,
 task_time,
 create_time,
 @rn := CASE WHEN @prev_task_id <> task_id THEN 1
        WHEN @prev_task_time <> task_time THEN 1
        ELSE @rn + 1 END AS rank,
 @prev_task_id := task_id,
 @prev_task_time := task_time
 FROM instances, (SELECT
                  @rn := 0,
                  @prev_task_id := -1,
                  @prev_task_time := '-1') t
 where task_id in (1209, 1211)
 having rank = 1 
 ORDER BY task_id, task_time DESC, create_time DESC;

Question comes After I add having rank = 1 clause. The returning result is not accurate compared with the result without having rank = 1 and is missing some records whose rank is also 1, for example, most records with task_id = 1211 are missing.

As I know , having clause is launched after select for add filter to the final records. I want to know the mistakes I've made, THX.

Why are you using `having`? Read about it. It will implictly `group by` all columns and if you `set sql_mode='only_full_group_by'` or use 5.7+ you will be told you are using non-standard behaviour since it doesn't make sense to `select` a column not grouped on. Whatever you want, this query isn't doing it. — philipxy, Oct 21 '17 at 01:34
Possible duplicate of https://stackoverflow.com/q/34115174/3404097 — philipxy, Oct 21 '17 at 01:48
Thanks for all of your help. In my opinion, `having` works for the aggregate values. If `set sql_mode='only_full_group_by' is `not set, it doesn't work correctly and becomes undefined behavior.Is it? — chenhao li, Oct 22 '17 at 15:56

Gordon Linoff · Answer 1 · 2017-10-23T11:42:40.203

MySQL does not guarantee the order of evaluation of expressions in a select. So, you cannot assign a variable and then use it in another expression -- and expect it to do what you want.

I would recommend a subquery:

SELECT x.*
FROM (SELECT task_id, task_time, create_time,
              (@rn := (CASE WHEN @p = concat_ws(':', task_id, task_time) THEN 1
                            WHEN @p := concat_ws(':', task_id, task_time) THEN @rn + 1
                            ELSE @rn + 1
                       END)
              ) AS rank,
       FROM instances CROSS JOIN
            (SELECT @rn := 0, @p := '') params
       WHERE task_id in (1209, 1211)
       ORDER BY task_id, task_time DESC, create_time DESC;
      ) x
 WHERE rank = 1 ;

Notes:

In the most recent versions of MySQL, you need an additional subquery. ORDER BY does not work with variables without the subquery.
MySQL does not guarantee the order of execution of expressions in a SELECT, so you should do all the assignments in one subquery.
You can combine the expression that defines the groups into a single variable.

Thanks for your help! Your sql works fine (although some small mistakes in it). Furthermore, I wonder know that why `having` doesn't work. Whether just as the comment said, it is because my setting for mysql doesn't implicitly group all the columns and becomes undefined behavior. — chenhao li, Oct 23 '17 at 03:10

mysql `having` operation with specific @variable value

1 Answers1