SQL Syntax for selecting a group of 'last' records
Posted on 2015-01-20
I have a table, tblPieces that has a PieceID and many other detail about each chunk of iron (eventually 10K or so chunks)
I have tblFlowLineReadings, that has PieceID, and JobID in it as foreign keys, along with data about a particular reading
(eventually millions of readings)
I have a form that let's the users choose a subset of PieceIDs to put on a report.
Those choices get knocked into a table, tempOnReport.
Now, for each PieceID in tempOnReport, I want to retrieve the latest row in tblFlowLineReadings for that piece.
Getting ALL the rows in tblFlowLineReadings would be simple enough
Select * from tblFlowLineReadings where PieceID in (Select PieceID from tempOnReport)
But getting ONLY the latest row for each PieceID, that's a bit tricky...
Eventually I'll need to make this same result set come out for an ASP.Net page, so anything Access specific, or SQL Server naughty, won't cut the mustard.
The definition of 'latest' can be the largest JobID in tblFlowLineReadings for each PieceID in tempOnReport.