SQL Returning rows with max value in column, within a specific range

Question

I'm populating a DataTable in C# using an OleDbDataAdapter, and I am trying to get a query to work without much success.

The communication to/from the server works fine, as is evidenced by the simple query that returns all the records without any filter:

var commandText = string.Format("SELECT IsoShtRevID, LineID, Filename, Revision " +
                                "FROM dbo.PDSIsometricSheets WHERE SchemaName='{0}'", projectNo);

This gives me a list of about 8000 entries, however there is some redundancy.

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.

Here are a few of the attempts I've tried so far:

var commandText = string.Format("SELECT * FROM 
                               (SELECT max(Revision) as LatestRev
                                FROM dbo.PDSIsometricSheets) 
                                WHERE Revision < 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);

Here are the questions I've visited so far trying to get this to work:

SQL Select only rows with Max Value on a Column

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

Fetch the row which has the Max value for a column

Select Rows with Maximum Column Value group by Another Column

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.

UPDATE:

Here's the latest iteration, using some advice from below:

var commandText = string.Format("SELECT IsoShtRevID, LineID, Filename, MAX(Revision) as MaxRevision " +
                                "FROM dbo.PDSIsometricSheets " +
                                "WHERE SchemaName='{0}' AND Revision <= 5 AND Revision >= 0" +
                                "GROUP BY IsoShtRevID, LineID, Filename", projectNo);

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...

Answer 1

Finally got it thanks to the comments, reading more SQL, and viewing my commandstring diligently at runtime for typos.

var commandText = string.Format("SELECT T1.IsoShtRevID, T1.LineID, T1.FileName, T1.Revision " +
                                "FROM dbo.PDSIsometricSheets T1 " +
                                "INNER JOIN (" +
                                    "SELECT LineID, MAX(Revision) as MaxRevision " +
                                    "FROM dbo.PDSIsometricSheets " +
                                    "WHERE SchemaName='{0}' AND Revision <= 5 AND Revision >= 0" +
                                    "GROUP BY LineID" +
                                ") T2 " +
                                "ON T1.LineID = T2.LineID AND T1.Revision = T2.MaxRevision ", projectNo);