title: Select multiple rows from a table where field is the max date
body: <p>I have a table called Product. I need to select all product records that have the MAX ManufatureDate.</p>
  
  <p>Here is a sample of the table data:</p>
  
  <pre><code>Id  ProductName     ManufactureDate
  1   Car             01-01-2015
  2   Truck           05-01-2015
  3   Computer        05-01-2015
  4   Phone           02-01-2015
  5   Chair           03-01-2015
  </code></pre>
  
  <p>This is what the result should be since the max date of all the records is 05-01-2015 and these 2 records have this max date:</p>
  
  <pre><code>Id  ProductName     ManufactureDate
  2   Truck           05-01-2015
  3   Computer        05-01-2015
  </code></pre>
  
  <p>The only way I can think of doing this is by first doing a query on the entire table to find out what the max date is and then store it in a variable @MaxManufatureDate. Then do a second query where ManufactureDate=@MaxManufactureDate. Something tells me there is a better way.</p>
  
  <p>There are 1 million+ records in this table:</p>
  
  <p><strong>Here is the way I am currently doing it:</strong></p>
  
  <pre><code>@MaxManufactureDate = select max(ManufactureDate) from Product
  select * from Product where ManufactureDate = @MaxManufactureDate
  </code></pre>
  
  <p>If figure this is a lot better then doing a subselect in a where clause. Or is this the same exact thing as doing a subselect in a where clause? I am not sure if the query gets ran for each row regardless or if sqlserver stored the variable value in memory.</p>