Creating a loop in a T-SQL if statement. 1

[pabowen]

I have been presented with an odd problem, and I was hoping for some help.

I need to create a loop of sorts to determine the first value in a column that is less than the supplied value.

Specifically I have two tables, the first table stores a volumeID (numeric), and then the FileNo (int)

The Second table stores the VolumeID (numeric) and the BaseFileNo (int)

The file number in table1 does not match directly to the BaseFileNo, it might be = to or greater than. I need to find the corresponding BaseFileNo which is the first value with the same volumeID, that is <= the FileNo. I need to take the BaseFileNo from Table 2 and store it in a 3rd Column in Table1 called BaseFileNo

Example of the two tables with corresponding rows.
TABLE1 TABLE2
VolumeID FileNo BaseFileNo VolumeID BaseFileNo
1 1234 ? 1 1230
1 1245 ? 1 1245
2 6543 ? 2 6538
etc....

I figured I could do this with some sort of a loop, but cannot figure out for the life of me how to do this in Transact SQL.

Thanks, I appreciate any help!

 

[smin]

Would this work?

select a.VolumeID, a.FileID, max(b.BaseFileID)
from table1 a left join table2 b
on a.VolumeID = b.VolumeID and
a.FileID >= b.BaseFileID
group by a.VolumeID, a.FileID

Using the data you posted, The result of the above code was;

VolumeID FileID BaseFileID
1 1234 1230
1 1245 1245
2 6543 6538

 

[pabowen]

That worked, Thanks - I was having a hard time figuring out how to do it that way. You have shown me the light.