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

Question

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

`group by ofr.product_name) as tt;` semicolon after tt is not necessary — Lukasz Szozda, Apr 08 '19 at 19:25
Do you have a table tt? The table alias tt can't be selected from in the sub-query - all according to ANSI SQL-92! — jarlh, Apr 08 '19 at 19:35

Gordon Linoff · Accepted Answer · 2019-04-08T20:05:14.000

0

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;

edited Apr 08 '19 at 20:05

answered Apr 08 '19 at 19:36

Gordon Linoff

1,242,037
58
646
786

@TheImpaler 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 – Kamran Hosseini Apr 08 '19 at 19:47
@k47 Let me spin up a vm and will try MySQL. – The Impaler Apr 08 '19 at 19:48

score 0 · Answer 2 · answered Apr 08 '19 at 19:59

The only solution that worked for me in MySQL 5.x needs to repeat your query. In MySQL 8.x you can use CTEs (Common Table Expressions) but that's not available in 5.x.

Anyway, here's the query that works:

select x.*
from (
  select product_name, count(*) as cnt
  from offers
  group by product_name
) x
join (
  select max(cnt) as ct
  from (
    select product_name, count(*) as cnt
    from offers
    group by product_name
  ) y
) z on z.ct = x.cnt

Result:

product_name  cnt
------------  ---
Daguerrotype  3

For reference, the data I used is:

create table offers (
  product_name varchar(30)
);

insert into offers (product_name) values ('Daguerrotype');
insert into offers (product_name) values ('Transistor radio');
insert into offers (product_name) values ('Victrola');
insert into offers (product_name) values ('Daguerrotype');
insert into offers (product_name) values ('Victrola');
insert into offers (product_name) values ('Daguerrotype');

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

2 Answers2