Selecting MAX value from a Database column.
-
Hi, I am using SQL database with VB. The database "company" has 1 column "EmpID" which I filled with 0,1,2,... so on, and "EmpID" column is in string format. Now I want to get the MAX of this "EmpID", for this I wrote a code like this in VB : SELECT MAX(EmpID) From company. I found that out of 0 to 150 EmpID in database, it is selecting MAX value as 9 and sometime as 99. I think there is something wrong with this. Also, If I test it for small data, say from 1 to 15, it is selecting correct MAX value. Can someone help me how to do this? My basic idea is to get the last "EmpID" which I will add and use it as next "EmpID". Thanks R.S.
-
Hi, I am using SQL database with VB. The database "company" has 1 column "EmpID" which I filled with 0,1,2,... so on, and "EmpID" column is in string format. Now I want to get the MAX of this "EmpID", for this I wrote a code like this in VB : SELECT MAX(EmpID) From company. I found that out of 0 to 150 EmpID in database, it is selecting MAX value as 9 and sometime as 99. I think there is something wrong with this. Also, If I test it for small data, say from 1 to 15, it is selecting correct MAX value. Can someone help me how to do this? My basic idea is to get the last "EmpID" which I will add and use it as next "EmpID". Thanks R.S.
I think that is because your EmpId id not an integer in your database. It something be varchar or something similar. You should change it to int if it can have only integer values. In order to make the query work in current condition, use following:
SELECT MAX(cast(EmpID as int)) From company
Note that this is not a good practice.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
-
Hi, I am using SQL database with VB. The database "company" has 1 column "EmpID" which I filled with 0,1,2,... so on, and "EmpID" column is in string format. Now I want to get the MAX of this "EmpID", for this I wrote a code like this in VB : SELECT MAX(EmpID) From company. I found that out of 0 to 150 EmpID in database, it is selecting MAX value as 9 and sometime as 99. I think there is something wrong with this. Also, If I test it for small data, say from 1 to 15, it is selecting correct MAX value. Can someone help me how to do this? My basic idea is to get the last "EmpID" which I will add and use it as next "EmpID". Thanks R.S.
-
Hi, I am using SQL database with VB. The database "company" has 1 column "EmpID" which I filled with 0,1,2,... so on, and "EmpID" column is in string format. Now I want to get the MAX of this "EmpID", for this I wrote a code like this in VB : SELECT MAX(EmpID) From company. I found that out of 0 to 150 EmpID in database, it is selecting MAX value as 9 and sometime as 99. I think there is something wrong with this. Also, If I test it for small data, say from 1 to 15, it is selecting correct MAX value. Can someone help me how to do this? My basic idea is to get the last "EmpID" which I will add and use it as next "EmpID". Thanks R.S.
Select MAX(cast(EmpId as int)) from TblName
OR
Select top 1 EmpId from TblName order by EmpId desc
:)
Niladri Biswas