Page 1 of 1

SQL Query - Latest Revision Items

Posted: Tue Jul 25, 2006 12:15 pm
by TheMoose
My project is sort of a 'document management' type piece, but more in-depth than just managing docs. It scans pages for barcodes, and does a lot of stuff with them. I am storing indexes for files in a MSSQL database. One of the things it does is groups pages together, etc. The user can upload multiple times as updates/revisions come in. What I'm aiming to do is get a recordset of all the pages (no duplicates) but each has to have the latest revision date for that respective page.

My database looks basically like:

auditDocuments:
ID
LoanID
LOID
Timestamp
Filename

auditDocumentPages:
ID
DocumentID
Size
Data
FormID
Timestamp

So I have a Loan, we'll give it loan number DEVNET-12345. Since each document may or may not have all the pages I need, I have to look at all pages for all documents for that loan number. So I need to get all auditDocument with LoanID=DEVNET-12345. But if two documents for this loan have page with FormID of 1234, I only want the the page for FormID=1234 that has the most recent Timestamp.

My query so far:

Code: Select all

SELECT * FROM auditDocumentPages INNER JOIN auditDocument ON auditDocumentPages.DocumentID=auditDocument.ID WHERE auditDocument.LoanID='someloan' AND
Any ideas? I'm stumped :(

Thanks!