BUG: order by desc on a decimal field

[Tom Locke]

The table looks like this:

t
f d
1 1
2 2
-2 -2
-3 -3
-3.456 -3.456
2.328 2.328


Where f is a double and d is a decimal.

The query on the f field looks like this:

SELECT t.f FROM t ORDER BY t.f DESC;

the results are:

q
f
2.328
2
1
-2
-3
-3.456


The same query run on the d field:

SELECT t.d FROM t ORDER BY t.d DESC;

produces this results:

q
d
-2
-3
-3.456
2.328
2
1


Which appears to be a bug.
I have tested this on several computers running Access 2002 and 2003, all
with the same results.

Tom Locke

 

[Brian Camire]

Allen Browne discusses this at:

http://members.iinet.net.au/~allenbrowne/bug-08.html

A apparent workaround would be:

SELECT t.d FROM t ORDER BY -t.d;

 

[Tom Locke]

Thanks. Never thought of the negative ascending sort.

Tom Locke

 

[Brian Camire]

Note that the workaround I suggested will handle NULL values differently.

Normally ORDER BY...DESC will return records with NULL values last.
However, the approach I suggested will return records with NULL values
first.