how to sort so that null values come last

Classic Servoy
1

Hi,

I have a foundset which I need to sort after a search. The column i’m sorting on is a varchar column. There can be null values in it. What I need to do is, to sort on this column so that the value ‘1’ always comes first. And null values come last. When I sort the foundset like foundset.sort(‘special_priority asc’), null values come first and then the rest is correct (e.g. null,null,null,1,2,3…).

Is there anyway of doing this?

Thanks in advance!

Regards,
Hareendra

2

Solutions to that are database specific for example:

http://sqlblog.com/blogs/denis_gobo/arc … /3048.aspx

3

Since we go that route. If you use PostgreSQL (Servoy 5.2 comes bundled with this) you can use the following syntax:

SELECT * FROM myTable ORDER BY myColumn ASC NULLS LAST

See http://www.postgresql.org/docs/8.4/stat … order.html

4

The servoy route would be: create a calculation

function field_to_sort_is_null()
{
	return field_to_sort ? 0 : 1
}

and then

foundset.sort("field_to_sort_is_null ASC, field_to_sort ASC")
5

michel:
The servoy route would be: create a calculation

That would only work if the calculation stored it’s results in a database column.
Servoy sorting is done on the backend database, not in RAM.

6

Hi,
I’m using Sybase DB. So is there no way of achieving this through Servoy?

Hareendra

7

the way Michel explained above will work (when field_to_sort_is_null is a stored column)

8

I think Servoy should play a role (as data intermediate between database and application) in the order of nulls when sorting.
Servoy claims to be database independent (what we appreciate very much) and now we face with the shift from Sybase to PostgreSQL a noticeable difference in the data presentation order !

A stored calculation is IMHO an unwanted workaround and no solution…

Maybe the sort options asc and desc can be extended (optional) with something as nulls last and nulls first

Regards,

9

if all or most databases has special syntax for them so that we can control that behavior then it could be done
But i think this is not the case, but i could be mistaken.

10

Hi Johan,

You could start with ANSI SQL. In ANSI SQL you have for example the function :

COALESCE

The COALESCE function returns the value of the first argument that does not evaluate to NULL.

COALESCE is an ANSI-defined shorthand for a specific use of the CASE function. Any COALESCE expression can also be expressed as a CASE function.

You can use the coalesce function in the (SQL) order by clause.
Example : select … order by coalesce(invoice_date, ‘1000-01-01’) desc

This code should be database independent (if ANSI SQL supported).

I suppose this is quite a challenge to implement in Servoy…

Regards,

11

Lambert,

This could give unexpected results (what max date to use then?) and possibly sql errors on some databases, so we leave the default ordering of nulls to the database.

Unfortunately, the default is different between sybase and postgres and you cannot configure this in postgres at server, database of session level, the default is hard-coded.

Maybe file a feature request with postgres?

Rob