title: SQL Returning rows with max value in column, within a specific range
body: <p>I'm populating a DataTable in C# using an <code>OleDbDataAdapter</code>, and I am trying to get a query to work without much success.</p>
  
  <p>The communication to/from the server works fine, as is evidenced by the simple query that returns all the records without any filter:</p>
  
  <pre><code>var commandText = string.Format("SELECT IsoShtRevID, LineID, Filename, Revision " +
                                  "FROM dbo.PDSIsometricSheets WHERE SchemaName='{0}'", projectNo);
  </code></pre>
  
  <p>This gives me a list of about 8000 entries, however there is some redundancy.</p>
  
  <p>There are multiple rows with the same LineID, but each one has a separate Revision value.  I'm trying to get only the rows with the highest revision for each LineID, from a range of 0 to 5.</p>
  
  <p>Here are a few of the attempts I've tried so far:</p>
  
  <pre><code>var commandText = string.Format("SELECT * FROM 
                                 (SELECT max(Revision) as LatestRev
                                  FROM dbo.PDSIsometricSheets) 
                                  WHERE Revision &lt; 5" , projectNo);
  
  
  var commandText = string.Format("SELECT T.IsoShtRevID, T.LineID, T.Filename, T.Revision
                                   FROM dbo.PDSIsometricSheets T
                                   WHERE Revision = 
                                         (SELECT MAX(T1.Revision)
                                          FROM dbo.PDSIsometricSheets T1
                                          WHERE T1.IsoShtRevID = T.IsoShtRevID
                                         )", projectNo);
  
  var commandText = string.Format("SELECT IsoShtRevID, LineID, Filename, MAX(Revision) as LatestRevision 
                                   FROM dbo.PDSIsometricSheets WHERE SchemaName='{0}'
                                   GROUP BY LineID, IsoShtRevID, Filename", projectNo);
  </code></pre>
  
  <p>Here are the questions I've visited so far trying to get this to work:</p>
  
  <p><a href="https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column">SQL Select only rows with Max Value on a Column</a></p>
  
  <p><a href="https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql">How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?</a></p>
  
  <p><a href="https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column">Fetch the row which has the Max value for a column</a></p>
  
  <p><a href="https://stackoverflow.com/questions/1425240/select-rows-with-maximum-column-value-group-by-another-column">Select Rows with Maximum Column Value group by Another Column</a></p>
  
  <p>Everything above either returns the same thing as my original query, or just errors out from bad syntax.  SQL is the furthest thing from my forte, and I'm trying to figure out if I'm limited in functionality by using a DataAdapter.  </p>
  
  <p>UPDATE:</p>
  
  <p>Here's the latest iteration, using some advice from below:</p>
  
  <pre><code>var commandText = string.Format("SELECT IsoShtRevID, LineID, Filename, MAX(Revision) as MaxRevision " +
                                  "FROM dbo.PDSIsometricSheets " +
                                  "WHERE SchemaName='{0}' AND Revision &lt;= 5 AND Revision &gt;= 0" +
                                  "GROUP BY IsoShtRevID, LineID, Filename", projectNo);
  </code></pre>
  
  <p>This filters out the revision to values between 0 and 5, however there are still multiple rows for LineID, each with different Revision numbers.  It's like the Max command is being ignored...  </p>