title: SQL Server query, get records with distinct value in 1 field and max in other field
body: <p>I have a table with some columns:</p>
  
  <pre><code>WON   param      |  machine   |    inputdate     |  val
  ---|-------------|------------|------------------|--------|
  18 | PAR.SPTDM.X1|    MM01    | 20/01/2013 12:43 |  2.5   |
  18 | PAR.SPTDM.Y1|    MM01    | 20/01/2013 12:43 |  3,4   |
  22 | PAR.SPTDM.X1|    MM01    | 22/01/2013 16:10 |  1.2   |
  22 | PAR.SPTDM.Y1|    MM01    | 22/01/2013 16:10 |  1.7   |
  33 | PAR.SPTDM.X1|    MM03    | 22/01/2013 16:13 |  2.34  |
  33 | PAR.SPTDM.Y1|    MM03    | 22/01/2013 16:13 |  2,21  |
  27 | PAR.LAS.PWR |    MM10    | 25/01/2013 08:14 |  100.5 |
  14 | PAR.LAS.UV  |    MM10    | 18/01/2013 17:27 |  134.8 |
  41 | PAR.LAS.UV  |    SLA4    | 27/01/2013 09:14 |  2,1   |
  62 | PAR.LAS.UV  |    SLA5    | 27/01/2013 11:15 |  14.6  |
  </code></pre>
  
  <p>Some of these rows get duplicated (for reasons unknown to me, it's the backend for a management system). Also the <code>value</code> column is a string, it needs conversion to numbers.</p>
  
  <p>The database is readonly, and I want to pull in data into an Excel sheet based on a few conditions:</p>
  
  <ul>
  <li>group by <code>machine</code> and <code>param</code></li>
  <li>filter on <code>param</code>: only <code>'PAR.SPTDM.%'</code></li>
  <li>filter on <code>machine</code>: only <code>'MM%'</code></li>
  <li>ignore val's with a comma, convert the rest to numbers</li>
  </ul>
  
  <p>This works without any problems using the following query:</p>
  
  <pre><code>SELECT DISTINCT t1.param, t1.machine, cast(t1.val as float), t1.inputdate
  FROM dbbackend t1
  WHERE (t1.machine Like 'MM%')
      AND (t1.param Like 'PAR.SPTDM.%')
      AND (t1.val&lt;&gt;'') And (t1.val not Like '%,%')
  </code></pre>
  
  <p>Now I want to only retrieve the records for each machine and each parameter (of interest) with the latest <code>inputdate</code>. First I tried</p>
  
  <pre><code>SELECT DISTINCT t1.param, t1.machine, max(cast(t1.val as float)), max(t1.inputdate)
  FROM dbbackend t1
  WHERE (t1.machine Like 'MM%')
      AND (t1.param Like 'PAR.SPTDM.%')
      AND (t1.val&lt;&gt;'') And (t1.val not Like '%,%')
  GROUP BY machine, param
  </code></pre>
  
  <p>But this gave me <code>val</code>'s which weren't in the dataset, correct machine/param/inputdate, but wrong val.</p>
  
  <p>Next try was</p>
  
  <pre><code>SELECT DISTINCT t1.param, t1.machine, cast(t1.val as float), t1.inputdate
  FROM dbbackend t1
  WHERE t1.WON IN
      (
      SELECT latestrec.WON FROM (
          SELECT DISTINCT max(t2.WON), t2.param, t2.machine, max(t2.inputdate)
          FROM dbbackend t2
          WHERE (t2.machine Like 'MM%')
              AND (t2.param Like 'PAR.SPTDM.%')
              AND (t2.val&lt;&gt;'') And (t2.val not Like '%,%')
              GROUP BY machine, param
          ) as latestrec
      )
  </code></pre>
  
  <p>But this wouldn't work, MS Query told me</p>
  
  <blockquote>
    <p><em>Could not add the table '('.</em></p>
  </blockquote>
  
  <p>So can someone tell me why I get wrong values with the first call, and why the second isn't working, or what is the correct method? I also suspect MS query to trip over that nested subquery :/</p>