Problem with ORDER BY involving ROUND

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem with ORDER BY involving ROUND

Gerry Blanchette
I received results in the wrong order when using ORDER BY on a column
computed with ROUND, and am interested in knowing if anyone has
encountered a similar problem. I have a solution to my problem at the
bottom of this email, but am not sure it should be necessary. I
appreciate all comments.


Given my table

sqlite> .dump foo
BEGIN TRANSACTION;
CREATE TABLE foo (Numerator INT NOT NULL, Denominator INT NOT NULL);
INSERT INTO "foo" VALUES(42, 1);
INSERT INTO "foo" VALUES(208, 1);
INSERT INTO "foo" VALUES(197, 1);
COMMIT;

Given this query and output it is obvious that the order of the returned
rows are not in the order one would expect:

sqlite> SELECT Numerator, Denominator, ROUND(((1.0 * Numerator) / (1.0 *
Denominator)), 0) AS MyAvg FROM foo ORDER BY MyAvg DESC;

Numerator       Denominator     MyAvg
42              1               42
208             1               208
197             1               197

Here, removing the ROUND returns the rows in the expected order.

SELECT Numerator, Denominator, ((1.0 * Numerator) / (1.0 * Denominator))
AS MyAvg FROM foo ORDER BY MyAvg DESC;

Numerator       Denominator     MyAvg
208             1               208
197             1               197
42              1               42

THE SOLUTION:
ROUND returns a STRING! If one wishes to so something similar using
ROUND, multiply by 1 (e.g. ROUND( a/b, 0 ) * 1).

Is there any reason why ROUND doesn't return a double?

Thanks!

-- Gerry Blanchette
Reply | Threaded
Open this post in threaded view
|

Re: Problem with ORDER BY involving ROUND

Dan Kennedy

> Is there any reason why ROUND doesn't return a double?

Because the IEEE storage format most (all?) computers
use to store floating point numbers is binary based, many
values that can be stored exactly in a base 10 system are
rounded slightly to fit into the binary system. This is
similar to the way the value 1/3 cannot be stored exactly
in a base 10 system.

You can see an example of this rounding using sqlite:

sqlite> select (0.5 - 0.4) - 0.1;
-2.77555756156289e-17

This is, incidentally, why you should be very careful comparing
floating point numbers in C using "==":

sqlite> select (0.5 - 0.4) == 0.1;
0

Google for "IEEE floating point format" if you require a coherent
explanation :)



               
__________________________________
Discover Yahoo!
Use Yahoo! to plan a weekend, have fun online and more. Check it out!
http://discover.yahoo.com/
Reply | Threaded
Open this post in threaded view
|

Re: Problem with ORDER BY involving ROUND

Stephen C. Gilardi
>> Is there any reason why ROUND doesn't return a double?
>
> Because the IEEE storage format most (all?) computers
> use to store floating point numbers is binary based, many
> values that can be stored exactly in a base 10 system are
> rounded slightly to fit into the binary system. This is
> similar to the way the value 1/3 cannot be stored exactly
> in a base 10 system.
>
> [more good advice about the inexactness of floats]

That's true, but the result of ROUND is an integer.  An IEEE double can
store a large range of integers exactly (something like +/- 2^52).

Here's a nice concise reference for the format:
<http://stevehollasch.com/cgindex/coding/ieeefloat.html>

I'm sure there's a good reason (perhaps insufficient range in +/- 2^52)
for ROUND not to return a double, but IEEE doubles do do pretty well
for storing and retrieving a decent range of integers exactly.

--Steve