Sql Server : How to use an aggregate function like MAX in a WHERE clause
Solution 1
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.
Solution 2
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)
Solution 3
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)
Solution 4
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)
Solution 5
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......
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Admin
Updated on September 23, 2020Comments
-
Admin almost 4 years
I want get the maximum value for this record. Please help me:
SELECT rest.field1 FROM mastertable AS m INNER JOIN ( SELECT t1.field1 field1, t2.field2 FROM table1 AS T1 INNER JOIN table2 AS t2 ON t2.field = t1.field WHERE t1.field3=MAX(t1.field3) -- ^^^^^^^^^^^^^^ Help me here. ) AS rest ON rest.field1 = m.field