HAVING without GROUP BY

by

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.

More Related Contents:

  1. Insert local .csv data in remote SQL Server via already working connection string
  2. How to delete duplicate rows in SQL Server?
  3. Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc
  4. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  5. Is SQL syntax case sensitive?
  6. How to use ANY instead of IN in a WHERE clause?
  7. Get day of week in SQL Server 2005/2008
  8. Getting result of dynamic SQL into a variable for sql-server
  9. Simple Query to Grab Max Value for each ID
  10. SQL Server: Extract Table Meta-Data (description, fields and their data types)
  11. How to find the employee with the second highest salary?
  12. SQL Server 2008: how do I grant privileges to a username?
  13. How do I add a foreign key to an existing SQLite table?
  14. How to Implement Referential Integrity in Subtypes
  15. How to insert a blob into a database using sql server management studio
  16. Equivalent function for DATEADD() in Oracle
  17. How to get other columns when using Spark DataFrame groupby?
  18. Is there AUTO INCREMENT in SQLite?
  19. Anonymous TABLE or VARRAY type in Oracle
  20. Name database design notation you prefer and why?
  21. Incorrect syntax near ”
  22. INNER JOIN in UPDATE sql for DB2
  23. Group by with CLOB in select-statement
  24. Do clustered indexes have to be unique?
  25. How to delete duplicate rows with SQL?
  26. what is the difference between GROUP BY and ORDER BY in sql
  27. Best way to test SQL queries [closed]
  28. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  29. Determine if one coordinate is in radius of another
  30. Oracle ‘Partition By’ and ‘Row_Number’ keyword

Leave a Comment