CODEWITHSUNDEEP
c#.netsqldatabaseoledb
Parrish Husband
Parrish Husband

Reputation: 3178

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

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

Upvotes: 1

Views: 2303

Answers (2)

Parrish Husband
Parrish Husband

Reputation: 3178

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);

Upvotes: 1

Icarus
Icarus

Reputation: 63966

Option 3 should work but if it doesn't is because FileName or IsoShtRevID change across Revisions. In that case, you can do this:

SELECT  a.IsoShtRevID ,
        a.LineID ,
        a.Filename ,
        a.Revision 
FROM    dbo.PDSIsometricSheets a
join (select max(Revision) as Revision, LineID 
      from  dbo.PDSIsometricSheets where SchemaName ='{0}' ) x 
     join a on a.Revision = x.Revision and a.LineID=x.LineID
WHERE   a.SchemaName = '{0}'

Upvotes: 3

Related Questions