Distinct Float - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Distinct Float

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-02-21 : 05:37:59

Hi, I have a query which uses Select Distinct on a table which contains some Float columns which is returning duplicate rows. I have narrowed the cause down to the float column. If I remove this from query it returns one row, if I include it - it returns two rows but the value displayed to in Float column is the same in both rows.
Eg
Select distinct a,b,[FloatColumn] where a=1 and b=2 (2 identical rows )
Select distinct a,b where a=1 and b=2 (1 row)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-02-21 : 06:01:18

The wonders of Float.. You may need something like:


SELECT TOP 1 
	a,b,[FloatColumn]
FROM
	(
		SELECT DISTINCT a,b FROM <table> WHERE a=1 AND b=2 
	) a
INNER JOIN <table> b
ON a.a = b.a
AND a.b = b.b

lappin
Posting Yak Master

182 Posts

Posted - 2011-02-21 : 06:12:23

The query in the example was just to display the error column, the actual query is much bigger. I used ROUND([floatcolumn],5) to fix error - I'm curious if people advise to replace float columns with decimal, rather than rounding in query.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-21 : 06:35:14

perhaps it would not be better for us to keep guessing about the data and structure of your table .. if possible come up with an example; the table's schema and the underlaying data of the columns a, b, floatColumn. Which results in the specified result as per above mentioned queries. So that explanation of the folks are in light of the same !!

Cheers
MIK

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-02-21 : 10:37:07

I had to smile when I saw this post, because I have run into the same problem, pulled out my hair, screamed at the computer screen, and then finally came to the same conclusion as you did - that is, use round function or cast it as decimal or something.

Initially I blamed Microsoft, but then again, they explicitly say this about data type float: "Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly."

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-21 : 13:23:36

@sunita, @lappin can you post some code through which i can understand this issue?

I tried to produce but found it to be ok !!! [:-s]

Declare @tab table (a varchar(10), b varchar(10), c float)
insert into @tab values ('X','Y',10.23665),('X','Y',10.23665)
select * from @tab --Two rows
Select distinct a,b,c from @tab where a='X' and b='Y' -- One Row
Select distinct a,b from @tab where a='X' and b='Y' -- One Row

Cheers
MIK

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-02-21 : 15:23:15

Mik, I have run into this problem when the data stored in the float column was the result of some type of calculation. I guess the results could also vary depending on the system you are using, and the SQL version you are using. The code below was run on a Windows 7 64 bit with SQL 2008.

create table #tmp(f float);

insert into #tmp values 
	(3.14159265358979323846264338327950288*1.0e-2),
	(3.14159265358979323846264338327950288*0.01)

select distinct f from #tmp;

select cast(f as varbinary) from #tmp;

drop table #tmp;

And, I got the following results

f
0.0314159265358979
0.0314159265358979

0x3FA015BF9217271A
0x3FA015BF92172719

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources