[Solved] get Max() in a varchar field.

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

SQL Server 2005 Forums

Transact-SQL (2005)

[Solved] get Max() in a varchar field.

Author

Topic

CVDpr
Starting Member

41 Posts

Posted - 2008-09-25 : 08:47:10

Hello there, where i work there is a table with this fields:

Version varchar(4) '00.1', '01.1' ,'01.8' etc..
Pcode varchar(5) 'Hnx78','78Hys' ect..

and i have to select Pcode where Version is the recent/new version.

I already do this:


select Version, Pcode, Dscrp from APCData with(nolock) where Version = (select max(Version) from APCData with(nolock))

is this is Ok or i better convert it to decimal like this:


select Version, Pcode, Dscrp from APCData with(nolock) where Version = (select max(convert(decimal(2,1),Version)) from APCData with(nolock))

thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-25 : 08:50:33

Why ask us? try both, see what works best for you. It all depends on how your data is stored and how you want it to be returned.

Keep in mind that the numeric comparison between 2.8 and 11.1 is very different from the string comparison between the two.

For numeric values: 2.8 is less than 11.1
For string values: 2.8 is GREATER THAN 11.1

- Jeff
http://weblogs.sqlteam.com/JeffS

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-09-25 : 09:05:43

It's safer converting to decimal. You're first query can break if someome typed in a version wrong, but then you have other problems.

Jim
declare @table table (col varchar(10))

insert into @table select '4..7.3' union select '4.0.1.'
select max(col) from @table

returns 4.0.1, but maybe the version should be 4.7.3?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 09:08:10

quote:
Originally posted by jimf

It's safer converting to decimal. You're first query can break if someome typed in a version wrong, but then you have other problems.

Jim
declare @table table (col varchar(10))

insert into @table select '4..7.3' union select '4.0.1.'
select max(col) from @table

returns 4.0.1, but maybe the version should be 4.7.3?

coding for bad data?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-09-25 : 09:20:56

Yup. I work for an insurance company and data integrity is an after thought. I found it's better to assume that there's something wrong with the underlying data, and there usually is.

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 09:26:49

quote:
Originally posted by jimf

Yup. I work for an insurance company and data integrity is an after thought. I found it's better to assume that there's something wrong with the underlying data, and there usually is.

Jim

i prefer doing validations mostly at front end. So that we will not have any instances of invalid or bad data in our db.

CVDpr
Starting Member

41 Posts

Posted - 2008-09-25 : 10:41:49

quote:
Originally posted by jsmith8858

Why ask us? try both, see what works best for you. It all depends on how your data is stored and how you want it to be returned.

Keep in mind that the numeric comparison between 2.8 and 11.1 is very different from the string comparison between the two.

For numeric values: 2.8 is less than 11.1
For string values: 2.8 is GREATER THAN 11.1

- Jeff
http://weblogs.sqlteam.com/JeffS

the data is always 4 caracteres: 00.0 not 0.0 (02.8) , 11.1 is greater than 02.8

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-25 : 11:20:17

Then it doesn't matter either way, do whichever you prefer. Just be sure that you only have valid numeric values in that column if you want to convert to decimal, otherwise you will get errors.

- Jeff
http://weblogs.sqlteam.com/JeffS

CVDpr
Starting Member

41 Posts

Posted - 2008-09-25 : 12:31:35

Ok thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources