Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using MAX, then capturing all except Max

Author  Topic 

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2005-09-26 : 13:45:48
I need to first capture the max version associated with a file name here is the meat

SELECT 
fileName,
f.folderName,
masterID,
iterationId,
iterationNumber version
FROM
fileIteration fi
JOIN iteration i on (i.iterationID = fi.fileIterationID)
JOIN vw_folderFileLink fl on (fl.fileMasterID = i.masterID)
JOIN folder f on (f.folderID = fl.folderID)

WHERE
fileName LIKE '429020652.idw'
--GROUP BY masterID, fi.FileName, f.FolderName, fileName, i.iterationID
ORDER BY masterID, version DESC
--*******end code ********--

that returns these results:

429020652.idw SubPumps 715 7521 7
429020652.idw SubPumps 715 7511 6
429020652.idw SubPumps 715 7497 5
429020652.idw SubPumps 715 7465 4
429020652.idw SubPumps 715 7398 3
429020652.idw SubPumps 715 7314 2
429020652.idw SubPumps 715 7313 1

First I need help just getting that top row but then I need to get all expect the top row selected.. I've tryed using MAX in several ways but I'm not getting what i want here :/ any help is appreciated

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-26 : 14:40:47
How about some sample source data?
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-26 : 15:05:55
You show 5 columns in the result set, 6 columns in the select list.

Are iterationNumber and Version separate columns?
Go to Top of Page

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2005-09-26 : 15:13:34
Nope version is the alias for iterationNumber (it renames the column head from iteration to version)
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-26 : 15:17:30
There is so much missing info - sample data, which columns belong to which tables, which makes this guess only an example of how to get the "first row" you are looking for

Something like


Posted - 09/26/2005 : 13:45:48 Show Profile Email Poster Visit Kbalz's Homepage Send Kbalz an AOL message Reply with Quote
I need to first capture the max version associated with a file name here is the meat

SELECT
fileName,
f.folderName,
masterID,
iterationId,
iterationNumber version
FROM
fileIteration fi
INNER JOIN iteration i on (i.iterationID = fi.fileIterationID) -- I changed this to INNER JOIN... OK?
INNER JOIN vw_folderFileLink fl on (fl.fileMasterID = i.masterID)
INNER JOIN folder f on (f.folderID = fl.folderID)

INNER JOIN ( -- This is a guess since no information was given about what columns are in what tables
SELECT iterationID, MAX(iterationNumber) As MaxIterationNumber
FROM iteration
GROUP BY iterationID
) X on X.iterationID = fi.fileIterationID


WHERE fileName LIKE '429020652.idw'
AND i.iterationNumber = X.MaxIterationNumber

ORDER BY masterID, version DESC
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-26 : 15:18:25
quote:
Originally posted by Kbalz

Nope version is the alias for iterationNumber (it renames the column head from iteration to version)

My bad. I usually write

iterationNumber AS Version
Go to Top of Page
   

- Advertisement -