52
  1. Is the following possible according to standard(!) SQL?
  2. What minimal changes should be neccessary in order to be conforming to the standard (if it wasn't already)?
  3. It works as expected in MySQL, iff the first row has the maximum value for NumberOfPages.
SELECT *
FROM Book
HAVING NumberOfPages = MAX(NumberOfPages)

The following is written in the standard:

HAVING <search condition>

  • Let G be the set consisting of every column referenced by a <column reference> contained in the <group by clause>.
  • Each column reference directly contained in the <search condition> shall be one of the following:
  1. An unambiguous reference to a column that is functionally dependent on G.
  2. An outer reference.

source

Can somebody explain me, why it should be possible according to the standard?

In MySQL, it perfectly works.

shafee
  • 15,566
  • 3
  • 19
  • 47
Adi
  • 521
  • 1
  • 4
  • 3

4 Answers4

35

Despite the Mimer Validator result, I don't believe yours is valid Standard SQL.

A HAVING clause without a GROUP BY clause is valid and (arguably) useful syntax in Standard SQL. Because it operates on the table expression all-at-once as a set, so to speak, it only really makes sense to use aggregate functions. In your example:

Book HAVING NumberOfPages = MAX(NumberOfPages)

is not valid because when considering the whole table, which row does NumberOfPages refer to? Likewise, it only makes sense to use literal values in the SELECT clause.

Consider this example, which is valid Standard SQL:

 SELECT 'T' AS result
   FROM Book
 HAVING MIN(NumberOfPages) < MAX(NumberOfPages);

Despite the absence of the DISTINCT keyword, the query will never return more than one row. If the HAVING clause is satisfied then the result will be a single row with a single column containing the value 'T' (indicating we have books with differing numbers of pages), otherwise the result will be the empty set i.e. zero rows with a single column.

I think the reason why the query does not error in mySQL is due to propritary extensions that cause the HAVING clause to (logically) come into existence after the SELECT clause (the Standard behaviour is the other way around), coupled with the implicit GROUP BY clause mentioned in other answers.

Sanghyun Lee
  • 21,644
  • 19
  • 100
  • 126
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
27

“When GROUP BY is not used, HAVING behaves like a WHERE clause.” The difference between where and having: WHERE filters ROWS while HAVING filters groups

SELECT SUM(spending) as totSpending
FROM militaryspending
HAVING SUM(spending) > 200000;

Result

totSpending
1699154.3

More detail, please consult https://dba.stackexchange.com/questions/57445/use-of-having-without-group-by-in-sql-queries/57453

Summer Wine
  • 371
  • 3
  • 3
5

From the standard (bold added from emphasis)

1) Let HC be the having clause. Let TE be the table expression that immediately contains HC. If TE does not immediately contain a group by clause, then “GROUP BY ()” is implicit. Let T be the descriptor of the table defined by the GBC immediately contained in TE and let R be the result of GBC.

With the implicit group by clause, the outer reference can access the TE columns.

However, the certification to these standards is very much a self-certification these days, and the example you gave would not work across all of the main RDBMS providers.

Andrew
  • 26,629
  • 5
  • 63
  • 86
-1

Yes We can write the SQL query without Group by but write the aggregate function in our query.

select sum(Salary) from ibs having max(Salary)>1000
Hrishikesh Kulkarni
  • 9