Sql Server : How to use an aggregate function like MAX in a 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)