Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sql Server : How to use an aggregate function like MAX in a WHERE clause

People also ask

Can you use Max in a WHERE clause?

Overview. The MAX() function is used with the WHERE clause to gain further insights from our data. In SQL, the MAX() function computes the highest or maximum value of numeric values in a column.

Can you use aggregate functions in WHERE clause?

An aggregate function can be used in a WHERE clause only if that clause is part of a subquery of a HAVING clause and the column name specified in the expression is a correlated reference to a group. If the expression includes more than one column name, each column name must be a correlated reference to the same group.

Why we Cannot use WHERE clause with aggregate functions like HAVING?

We cannot use the WHERE clause with aggregate functions because it works for filtering individual rows. In contrast, HAVING can works with aggregate functions because it is used to filter groups.

Which clause is similar to WHERE clause and used with aggregate functions?

Explanation: “HAVING” clause are worked similar as “WHERE” clause.


As you've noticed, the WHERE clause doesn't allow you to use aggregates in it. That's what the HAVING clause is for.

HAVING t1.field3=MAX(t1.field3)

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.


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:

select t1.a, t1.b, t1.c
from table1 t1
where t1.date = (select max(t1_max.date)
                 from table1 t1_max
                 where t1.id = t1_max.id)

Be sure to create a single dataset before using an aggregate when dealing with a multi-table join:

select t1.id, t1.date, t1.a, t1.b, t1.c
into #dataset
from table1 t1
join table2 t2
  on t1.id = t2.id
join table2 t3
  on t1.id = t3.id


select a, b, c
from #dataset d
join #dataset d_max
  on d.id = d_max.id
having d.date = max(d_max.date)
group by a, b, c

Sub query version:

select t1.id, t1.date, t1.a, t1.b, t1.c
into #dataset
from table1 t1
join table2 t2
  on t1.id = t2.id
join table2 t3
  on t1.id = t3.id


select a, b, c
from #dataset d
where d.date = (select max(d_max.date)
                from #dataset d_max
                where d.id = d_max.id)

SELECT rest.field1
FROM mastertable as m
INNER JOIN table1 at t1 on t1.field1 = m.field
INNER JOIN table2 at t2 on t2.field = t1.field
WHERE t1.field3 = (SELECT MAX(field3) FROM table1)

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......


But its still giving an error message in Query Builder. I am using SqlServerCe 2008.

SELECT         Products_Master.ProductName, Order_Products.Quantity, Order_Details.TotalTax, Order_Products.Cost, Order_Details.Discount, 
                     Order_Details.TotalPrice
FROM           Order_Products INNER JOIN
                     Order_Details ON Order_Details.OrderID = Order_Products.OrderID INNER JOIN
                     Products_Master ON Products_Master.ProductCode = Order_Products.ProductCode
HAVING        (Order_Details.OrderID = (SELECT MAX(OrderID) AS Expr1 FROM Order_Details AS mx1))

I replaced WHERE with HAVING as said by @powerlord. But still showing an error.

Error parsing the query. [Token line number = 1, Token line offset = 371, Token in error = SELECT]