a query from SQL/92 doesn’t work in later versions

I have a sql query like this:

select tt.product_name, tt.countt
from (select ofr.product_name as product_name, count(*) as countt
from offers ofr
group by ofr.product_name) as tt
where 12 = (select max(tt.countt) from tt);

my problem is in the last line: the sql doesn’t recognize table tt!

As I know in SQL/92 this usage of tables works. But I don’t know what alternative should I use in the later versions.

I’m using this version of MY-SQL:

mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper

UPDATE: I want the row in tt which it’s “countt” is maximum amoung all rows in tt. The number “12” was an example, because based on the data in my dabase the max value of “count” column will be 12

Answer

I don’t understand what the max() is intended to be doing. I would be surprised if this ever worked in MySQL.

Perhaps you intend:

select tt.product_name, tt.countt
from (select ofr.product_name as product_name, count(*) as countt
      from offers ofr
      group by ofr.product_name
     ) tt
where 12 = tt.countt;

The subquery is not necessary for this logic. You could use a HAVING clause instead.

EDIT:

If you want the maximum value, you can use ORDER BY and LIMIT:

select ofr.product_name as product_name, count(*) as countt
from offers ofr
group by ofr.product_name
order by countt desc
limit 1;