SQL Query - Latest Revision Items
Posted: Tue Jul 25, 2006 12:15 pm
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:
Any ideas? I'm stumped 
Thanks!
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' ANDThanks!