0

I want to understand how the queries are parsed and get a general view about the query execution order in Mysql

I have a table 'Likes', and I want to find the most liked photo,

this query is ok 

select photo_id, count(*) as count from likes group by photo_id order by count desc limit 1;

but I wanted to know why this query is given me unwanted results, As if I wrote the query without the having clause at the end, a simple count and group by, not executing the max function, or more likely execute it for each distinct row separately:

select photo_id,count(*) as count from likes group by photo_id having max(count) ;

I read here: MySQL query / clause execution order about the execution order, but why can't I use max inside the having clue?

please share with me a good resource on how to understand Mysql queries if you have one :)

NyaSol
  • 537
  • 1
  • 5
  • 21
  • What sort of "unwanted results"? – Lightness Races in Orbit Apr 22 '19 at 14:04
  • As if I wrote the query without the having clause at the end, a simple count and group by, not executing the max function, or more likely execute it for each distinct row separately. – NyaSol Apr 22 '19 at 14:09
  • I think the second query won't be work. will throw an error. because you use aggregate function contain aggregate function – D-Shih Apr 22 '19 at 14:10
  • 1
    `HAVING` is applied after `GROUP BY`, in contrast to `WHERE` which is applied before it. `HAVING` is used to restrict the results returned by `GROUP BY`. – KIKO Software Apr 22 '19 at 14:12
  • `having max(count)` and then you miss an operator. That's not how `HAVING` is used, and even if you managed to get it to work - it'd be slower than initial query. Why are you trying to rewrite the first one in the first place? Is it because you're optimizing? – Mjh Apr 22 '19 at 14:12
  • what you mean with `having max(count)` ? – A.Marwan Apr 22 '19 at 14:46

0 Answers0