June 18, 2007 at 8:55 am
Hello people,
I have two tables, one for documents and one for files. I am attempting to retrieve one particular documents details with the latest associated file details. i.e
Documents table:
(pk)documentID, name, purpose etc
Files table:
(pk)fileID, (fk)documentID, name, fileSize, version, date published, etc
i want to pull a document with its latest version
i.e.
(Doc table)"Test Document", (doc table)"A document with no real purpose", (file table)20Mb, (file table - Version) 4, (file table)25/12/0000, 00:00:01
my current sp so far is:
SELECT
Documents.Name,
purpose
,
Files
.name,
Files
.FileSize,
Files
.Version,
Files.DatePublished
FROM Documents
INNER JOIN Files on Documents.documentID = files.DocumentID
WHERE ProjectID = @projectID
But i need to include the fact somewhere in this sp that the File is the latest version i.e. the file with the highest version number. I've tried putting MAX(files.version) in places but i keep getting some sort of error.
Any help would be much appreciated
Lee
June 18, 2007 at 9:11 am
Please never say "some sort of error" Tell us what the error is. it's the only way to help you.
Two possible solutions:
SELECT
Documents.Name,
purpose,
Files.name,
Files.FileSize,
Files.Version,
Files.DatePublished
FROM
Documents
INNER JOIN(
Select
Max(Version) as Maxversion,
DocumentID
From
Files
Group By DocumentID) AS MAXFiles on Documents.documentID = MaxFiles.DocumentID
JOIN Files ON maxFiles.DocumentID = Files.DocumentID and maxFiles.MaxVersion = Files.Version
WHERE
ProjectID = @projectID
or
SELECT
Documents.Name,
purpose,
Files.name,
Files.FileSize,
max(Files.Version) as Version,
Files.DatePublished
FROM
Documents
INNER JOINFiles on Documents.documentID = Files.DocumentID
WHERE
ProjectID = @projectID
Group By
Documents.Name,
purpose,
Files.name,
Files.FileSize,
Files.DatePublished
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 18, 2007 at 9:29 am
Thank you Crispin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply