title: Sql Server : How to use an aggregate function like MAX in a WHERE clause
body: <p>I want get the maximum value for this record. Please help me:</p>
  
  <pre><code>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
  </code></pre>