Hello, I'm trying to build a query with order by that puts nulls last always. I tryed to use the "NULLS LAST" expression but apparently it's not supported by monetdb. So i came up with something like this: select name, sum(number) from test group by name order by case when sum(number) is null then 1 else 0 end, sum(number) ASC Thing is, this works when the ordering column (number) is not aggretated, as soon as i throw the sum in there it returns no results. The sum of null values seems to be returning 0, is it possible to change that behaviour trough configuration? Is there a better way to achieve this? thanks
Hi Hilario, I've found your post about this problem also on Mondrian lists and I tried investigate it because I'm interested in implementation Mondrian on top of MonetDB. Regarding "case when" it seems that it is a bug. See http://bugs.monetdb.org/show_bug.cgi?id=3388 Regarding sum of null values. Zero in this case is definitively wrong result but I could reproduce this bug only after upgrade Monetdb without dump and reload of my database. For example if I created db in Feb2013-SP4 (?) and upgrade it to Feb2013-SP5 problem existed. If i created fresh db in Feb2013-SP5 I couldn't reproduce this bug. Best regards Klaudiusz On 16.10.2013 19:09, Hilario Fernandes wrote:
Hello,
I'm trying to build a query with order by that puts nulls last always.
I tryed to use the "NULLS LAST" expression but apparently it's not supported by monetdb.
So i came up with something like this:
select name, sum(number) from test group by name order by case when sum(number) is null then 1 else 0 end, sum(number) ASC
Thing is, this works when the ordering column (number) is not aggretated, as soon as i throw the sum in there it returns no results.
The sum of null values seems to be returning 0, is it possible to change that behaviour trough configuration? Is there a better way to achieve this?
thanks
participants (2)
-
Hilario Fernandes -
Klaudiusz