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
Advertisement
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;