title: Selecting rows with the nearest date using SQL
body: <p>I have a SQL  statement.</p>
  
  <pre><code>SELECT 
      ID, LOCATION, CODE,MAX(DATE),FLAG 
  FROM 
      TABLE1 
  WHERE 
       DATE &lt;= CONVERT(DATETIME,'11-11-2012') 
       AND EXISTS (SELECT * FROM #TEMP_CODE WHERE TABLE1.CODE = #TEMP_CODE.CODE) 
       AND ID IN (14, 279)
  GROUP BY 
       ID, LOCATION, CODE
  </code></pre>
  
  <p>I need rows with the nearest date to the 11-11-2012, but the table returns all the values.  What am I doing wrong. Thanks </p>
  
  <pre><code>ID     LOCATION              CODE            DATE              FLAG
  -------------------------------------------------------------------
  14  CAR STREET,UDUPI         234      2012-08-08 00:00:00.000   0
  14  CAR STREET,UDUPI         234      2012-08-10 00:00:00.000   1
  14  CAR STREET,UDUPI         234      2012-08-14 00:00:00.000   0
  279 MADHUGIRI                234      2012-08-08 00:00:00.000   1
  279 MADHUGIRI                234      2012-08-11 00:00:00.000   0
  </code></pre>
  
  <p>I want to show only the rows with dates less than or equal to the given date. The required result is </p>
  
  <pre><code>ID     LOCATION              CODE            DATE              FLAG
  -------------------------------------------------------------------
  14  CAR STREET,UDUPI         234      2012-08-10 00:00:00.000   1
  279 MADHUGIRI                234      2012-08-11 00:00:00.000   0
  </code></pre>