Decimal field will not sort properly

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)

Decimal field will not sort properly

Author

Topic

rbtaylor
Starting Member

2 Posts

Posted - 2007-06-25 : 18:08:51

Greetings Folks

I am posting this in the Transact SQL forum because it is using Transact SQL to create a temp table and dump this data in it.

I am stumped hard on this one. Have you ever had trouble with float fields not sorting correctly?

We are returning locations from a database that are within 2 miles of a point. I am using a temp table (I have to) and I have a distance field set to float (initially). From out of nowhere (or we did not notice it before) the locations are not sorting properly. They are all mixed up.
For example:

0.14
0.19
1.58
0.98
0.99
1.89
1.25

I would share the query with you but I know it is correct because the locations are correct. Its just that the list is out of order.

The distance from center for each point is collected in a field named "Distance" which is now set to decimal(38) I changed the data type of the Distance field in the temp table to decimal(38) to see
if it was related to the length of the distance value. That seemed to make the problem better but it did not solve it.

I am lost for words at this point. I cannot figure out why the distance field is not returning things in order of distance. Its almost like the Distance field is acting like a nvarchar field or something.

Got any ideas? I am almost laughing in sheer madness right now.

Thanks.....Rob

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-25 : 18:18:13

How many decimals? Use decimal(38, 2)

-- prepare sample data
declare @sample table (data float)

insert	@sample
select	0.14 union all
select	0.19 union all
select	1.58 union all
select	0.98 union all
select	0.99 union all
select	1.89 union all
select	1.25

-- show the expected output
select		data
from		@sample
order by	data

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-25 : 18:22:02

[code]-- prepare sample data
declare @sample table (data float)

insert @sample
select 0.14 union all
select 0.19 union all
select 1.58 union all
select 0.98 union all
select 0.99 union all
select 1.89 union all
select 1.25

-- show the abnormal output
select data
from @sample
order by cast(data as decimal(38))

-- show the right output
select data
from @sample
order by cast(data as decimal(38, 2))[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-25 : 18:31:21

Here is how floats are stored internally
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81849

It explains the "unexpected" sort order.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources