aggregate functions in where clause
If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view. Many (but not all) aggregate functions that take a single argument accept these clauses: 1  DISTINCT and UNIQUE, which are synonymous, cause an aggregate function to consider only distinct values of the argument expression. 2  ALL causes an aggregate function to consider all values, including all duplicates. The correct way to use max in the having clause is by performing a self join first: select t1.a, t1.b, t1.c from table1 t1 join table1 t1_max on t1.id = t1_max.id group by t1.a, t1.b, t1.c having t1.date = max(t1_max.date) The following is how you would join with a subquery: In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group. Some aggregate functions allow the windowing_clause, which is part of the syntax of analytic functions. Refer to windowing_clause for information about this clause. In the listing of aggregate functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*) Aggregate Functions . Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. Sql Server : How to use an aggregate function like MAX in a WHERE clause. 1  You could use a sub query... 2  But I would actually move this out of the where clause and into the join statement, as an AND for the ON clause. As a side note (5+ years later), the HAVING clause is actually intended for use with grouped elements. However, it works with aggregate clauses (MIN, MAX, AVG) in most SQL engines even without a GROUP BY clause. Sub query version: yes you need to use a having clause after the Group by clause , as the where is just to filter the data on simple parameters , but group by followed by a Having statement is the idea to group the data and filter it on basis of some aggregate function...... Sql Server : How to use an aggregate function like MAX in a WHERE clause. You could use a sub query... WHERE t1.field3 = (SELECT MAX(st1.field3) FROM table1 AS st1) But I would actually move this out of the where clause and into the join statement, as an AND for the ON clause.
Use aggregate functions in the HAVING clause.