Last Numeric Record - SQL Server Forums

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

Last Numeric Record

Author

Topic

baze7
Yak Posting Veteran

58 Posts

Posted - 2009-10-12 : 16:39:29

What is the correct way to get the last (highest value) if a record? I have a field "Item" it contains both numeric and char. I want to return the highest numeric value.

Thanks
Chad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-12 : 16:43:40

Are you using SQL Server 2005? If you are, then you can utilize the ROW_NUMBER function. Let us know.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

baze7
Yak Posting Veteran

58 Posts

Posted - 2009-10-12 : 16:44:23

Yes, SQL 2005

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-12 : 16:51:11

"correct" way is not to combine different logical datatypes in the same column

Since you have numeric data stored in a character type column then you'll have to, within a statement, first filter out all non-numeric data, the convert the remaining values to a numeric value, then take the MAX value. The first part is the tricky part. The simplest code would be to use isNumeric but that is not entirely reliable. You can try this but it may likely need revisions:


select max(convert(numeric(10,4), val)) as maxNumeric
from   (
       select val
       from   yourTable
       where  isNumeric(val) = 1 --NOT RELIABLE for all values
       ) as d

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-13 : 01:31:19

<<
NOT RELIABLE for all values
>>

Having said that, try this too


select max(convert(numeric(10,4), val)) as maxNumeric
from   (
       select val
       from   yourTable
       where  val not like '%[^0-9.]%' = 1 
       ) as d

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-13 : 10:18:50

quote:

Originally posted by madhivanan

<<
NOT RELIABLE for all values
>>

Having said that, try this too


select max(convert(numeric(10,4), val)) as maxNumeric
from   (
       select val
       from   yourTable
       where  val not like '%[^0-9.]%' -- = 1 
       ) as d

Of course that is not reliable either but potentially usefull to help eliminate values that won't convert to numeric.

'1.2.3.4' will break it

bottom line is: store properly typed values in their appropriately typed columns

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-13 : 10:41:10

Ok. In that case

1

select max(convert(numeric(10,4), val)) as maxNumeric
from   (
       select val
       from   yourTable
       where  val not like '%[^0-9.]%' and len(val)-len(replace(val,'.',''))<=1
       ) as d


select max(convert(numeric(10,4), val)) as maxNumeric
from   (
       select val
       from   yourTable
       where  isnumeric(val+'.d0')=1
       ) as d

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources