title: Return value at max date for a particular id with null as maximum value in t-sql
body: <p>I have this table.</p>
  
  <pre><code>
  ID       Date       Value 
  ___      ____       _____
  3241     9/17/12    5
  3241     9/16/12    100
  3241     9/15/12    20
  4355     9/16/12    12
  4355     9/15/12    132
  4355     9/14/12    4
  1001     NULL       89
  1001     9/16/12    125
  5555     NULL       89
  1234     9/16/12    45
  2236     9/15/12    128
  2236     9/14/12    323
  2002     9/17/12    45 
  </code></pre>
  
  <p>I would like to select the maximum date grouped by id and including <em>NULL</em> as maximum value that should be in the result to get something like that.</p>
  
  <pre><code>
  ID       Date       Value 
  ___      ____       _____
  3241     9/17/12    5
  4355     9/16/12    12
  1001     9/16/12    125
  5555     NULL       89
  1234     9/16/12    45
  2236     9/15/12    128
  2002     9/17/12    45
  </code></pre>
  
  <p>I found a solution but that not include <em>NULL</em> as maximum value 
  the solution by @bluefeet 
  <a href="https://stackoverflow.com/questions/12464669/return-value-at-max-date-for-a-particular-id/12464719?noredirect=1#comment76431959_12464719">Return value at max date for a particular id</a></p>
  
  <pre><code>
  SELECT t1.id,
         t2.mxdate,
         t1.value
  FROM yourtable t1
  INNER JOIN
    ( SELECT max(date) mxdate,
             id
     FROM yourtable
     GROUP BY id) t2 ON t1.id = t2.id
  AND t1.date = t2.mxdate
  
  </code></pre>
  
  <p>I also search for a solution to see how can we select <em>NULL</em> maximum value in <em>t-sql</em> so i found this solution by @Damien_The_Unbeliever <a href="https://stackoverflow.com/questions/21286215/how-can-i-include-null-values-in-a-min-or-max">How can I include null values in a MIN or MAX?</a></p>
  
  <pre><code>
  SELECT recordid,
         MIN(startdate),
         CASE
             WHEN MAX(CASE
                          WHEN enddate IS NULL THEN 1
                          ELSE 0
                      END) = 0 THEN MAX(enddate)
         END
  FROM tmp
  GROUP BY recordid
  </code></pre>
  
  <p>But i m stuck i don't know how to merge between this two solution to get what i want.</p>
  
  <p>PS: I m using <strong>SQL SERVER 2008</strong></p>