0

My requirements are: I now have a table, I need to group according to one of the fields, and get the latest record in the group, and then I search the scheme on the Internet,

SELECT
* FROM(
SELECT
* 
FROM
record r 
WHERE
r.id in (xx,xx,xx) HAVING 1
ORDER BY
r.time DESC 
) a 
GROUP BY
a.id

, the result is correct, but I can't understand the meaning of "having 1" after the where statement. I hope a friend can give me an answer. Thank you very much.

ysth
  • 96,171
  • 6
  • 121
  • 214
bronson
  • 1
  • Does this answer your question? [HAVING without GROUP BY](https://stackoverflow.com/questions/6924896/having-without-group-by) – OldProgrammer Jun 08 '22 at 01:40
  • In MySQL any numeric value different from zero is evaluated as true when used as a predicate. In this case `HAVING 1` means `HAVING true` and, therefore, is redundant. – The Impaler Jun 08 '22 at 01:57

1 Answers1

0

It does nothing, just like having true would. Presumably it is a placeholder where sometimes additional conditions are applied? But since there is no group by or use of aggregate functions in the subquery, any having conditions are going to be treated no differently than where conditions.

Normally you select rows and apply where conditions, then any grouping (explicit, or implicit as in select count(*)) occurs, and the having clause can specify further constraints after the grouping.

Note that your query is not guaranteed to give the results you want; the order by in the subquery in theory has no effect on the outer query and the optimizer may skip it. It is possible the presence of having makes a difference to the optimizer, but that is not something you should rely on, certainly from one version of mysql to another.

ysth
  • 96,171
  • 6
  • 121
  • 214