Select Max +1 not seeing the true max.

[nsanto17]

I have the following code set up and it was working perfectly until we added more then 10 branchs. Basicly when we went to add the 11th branch it assigned the branch id of 10. For somereason the Select Max(Branch_Num) is seeing the max of 9 when there are in fact 10 Branchs entered.

Does anyone have any clue on this one?


SELECT max(Branch_Num)+1 as MaxID
FROM dbo.Wholesale_Brokers
WHERE Broker_Code = 'D00010'

 

[MarkSweetland]

Branch_Num start at zero?


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[nsanto17]

Yes they do....

 

[gmmastros]

The problem is that the Branch_Num is a string column and not an integer column (like it should be).

copy/paste the following code to a query window to see what I mean.

Declare @Temp Table(StringColumn VarChar(10), IntColumn Int)

Insert Into @Temp Values('1',  1)
Insert Into @Temp Values('8',  8)
Insert Into @Temp Values('9',  9)
Insert Into @Temp Values('10', 10)
Insert Into @Temp Values('11', 11)

Select * From @Temp Order By StringColumn

Select * From @TEmp Order BY IntColumn

Select Max(StringColumn) As MaxStringColumn, Max(IntColumn) As MaxIntColumn
From   @Temp

My suggestion is to change the data type of the Branch_Num column to integer (if you can).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nsanto17]

If i change it to an integer will that screw up the data in that column?

 

[gmmastros]

Actually, you do need to be careful about doing this.

Before you do this, I would make sure that you have a good backup of the database just in case something bad happens.

Then, you should make sure that all of the data in the column (for this table) can be converted to an integer.

SELECT *
FROM dbo.Wholesale_Brokers
WHERE IsNumeric('-' + Branch_Num + '.0e0') = 0

If you run the code above, it should show you all of the rows in the table where the Branch_Num is not a positive integer value. If this returns any data, let me know, because it means you should NOT change the data type.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom