SQL Query - Latest Revision Items

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
TheMoose
Forum Contributor
Posts: 351
Joined: Tue May 23, 2006 10:42 am

SQL Query - Latest Revision Items

Post 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!
Post Reply