Hi,

Is it possible to sort a column of integers such that 'null' values are considered greater than actual values instead of the other way around?

The only way I know how to do this is to have a case statement which assigns '1' to rows with a null in the column and '0' to the others and then order on this first.

But I understand that case is not currently supported. I'm using Sql Server 2000.

Cheers,

Ian.

No that's not possible at the moment, although I don't think there will be a solution, other than faking values with CASE, as IMHO there is no setting/hint to order values in such a way that NULL's are considered bigger/smaller.

The 'workaround' could be that you execute the query twice: once with a filter that filters on NULL (and no sorting, just grab the NULL values) and the second time you query with the order by and you filter NULL's out.

Thanks for your reply.

The 'workaround' could be that you execute the query twice: once with a filter that filters on NULL (and no sorting, just grab the NULL values) and the second time you query with the order by and you filter NULL's out.

Yes. I think this would require both selects occur within a transaction with repeatable read to stop items potentialy appearing in both sets due to someone making an update at the same time.

Ian wrote:

Hi,

Is it possible to sort a column of integers such that 'null' values are considered greater than actual values instead of the other way around?

The only way I know how to do this is to have a case statement which assigns '1' to rows with a null in the column and '0' to the others and then order on this first.

But I understand that case is not currently supported. I'm using Sql Server 2000.

Cheers,

Ian.

You can implement a post-fetch/client-side sort with a custom IComparer. Check the docs at Using the Generated Code -> Adapter -> Using the entity collection classes -> Client side sorting.

Jeff...