Sql Server : How To Use An Aggregate Function Like Max In A Where Clause

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

Solution 1:

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 2:

You could use a sub query...

WHERE t1.field3 = (SELECTMAX(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 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
groupby 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#datasetfrom table1 t1
join table2 t2
  on t1.id = t2.id
join table2 t3
  on t1.id = t3.id


select a, b, c
from#dataset djoin#dataset d_maxon d.id = d_max.id
having d.date = max(d_max.date)
groupby a, b, c

Sub query version:

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


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

Solution 4:

SELECT rest.field1
FROM mastertable as m
INNERJOIN table1 at t1 on t1.field1 = m.field
INNERJOIN table2 at t2 on t2.field = t1.field
WHERE t1.field3 = (SELECTMAX(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......