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.
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.
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.
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]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With