MySQL Bugs: #4065: remove the need to mention column name in select in order to use it in having

Bug #4065	remove the need to mention column name in select in order to use it in having
Submitted:	8 Jun 2004 21:31	Modified:	31 Jan 2020 13:38
Reporter:	Gökhan Demir	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	all	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
When I want to use a column in having clause, MySQL forces me to mention the column name in the select. This causes inconsistent select statements with other database vendors. I would like to be able to write this query:

select a from t group by a having b = max(b)

In MySQL environments currently, I have to mention (and therefore select) the column name b in the select clause, such that:

select a, b from t group by a having b = max(b)

I dont't need the value of b, so why should I select it?

How to repeat:
select a from t group by a having b = max(b)

Suggested fix:
Remove the need of specifying the column in the select list if that column is to be used in the having part of the group by select statement. Thanks.

I think that this query is wrong in standard Sql:

select a from t group by a having b = max(b)

because  `b` is not in the GROUP BY clause. So, your query should be:

SELECT a FROM t WHERE b=(SELECT MAX(b) FROM t) GROUP BY a

I think that HAVING is not intended for fields wich are not in the GROUP BY clause.

Hi Federico,

All major db vendors use the syntax I have provided. In my opinion, the subquery attempt is just a workaround, and one of the side effects of it is the increasing query (and therefore the select max ... subquery) size. Plus, the syntax is pretty readable, understandable, therefore should be easily implementable in the code.

Gokhan

Thank you for a feature request. 

Please, name at least one db vendor that allows statements like this:

select a, b from t group by a having b = max(b)

Please, just copy and paste the table definition, this query and results it will give you on some sample data. From the vendor's command line tool window.

Scripts for Sybase ASE.

if exists( select * from sysobjects where type = 'U' and name = "t" )
   drop table t
go

create table t (a int not null, b int not null, primary key clustered (a, b) )
go

insert into t (a, b) values (1, 1)
go
insert into t (a, b) values (1, 2)
go
insert into t (a, b) values (1, 3)
go
insert into t (a, b) values (2, 2)
go
insert into t (a, b) values (2, 4)
go
insert into t (a, b) values (2, 6)
go
insert into t (a, b) values (3, 3)
go
insert into t (a, b) values (3, 6)
go
insert into t (a, b) values (3, 9)
go

select a, b from t group by a having b = max(b)
go

a           b           
----------- ----------- 
          1           3 
          2           6 
          3           9 

select * from t
go

a           b           
----------- ----------- 
          1           1 
          1           2 
          1           3 
          2           2 
          2           4 
          2           6 
          3           3 
          3           6 
          3           9 

Best Regards,
Gokhan Demir

OK. I believe, MS SQL will also support something like this then. May be nice to have this feature just to simplify migration to MySQL.

[10 Oct 2019 12:37] Roy Lyseng 

This query is invalid in standard SQL.

The HAVING clause may only refer to aggregated expressions, fields in the GROUP BY clause and other fields functionally dependent on the latter.

The reference to "b" in the HAVING clause is invalid since it refers to a group for which there are multiple "b" values.