Query for Select with Order by ASC and nulls first

[Former Member]

Hello,

I would to know how I could make a query to have the NULLS FIRST in a SELECT by keeping the ascending values.

Example:

SELECT START_DATE FROM MY_TABLE ORDER BY START_DATE

I have currently this result:

START_DATE

2011-06-30 11:30:30.0

2012-06-30 11:30:30.0

?

?

and I want to have this:

START_DATE

?

?

2011-06-30 11:30:30.0

2012-06-30 11:30:30.0

Thanks a lot

Nicolas

[lbreddemann]

Hi there,

there is no NULLS FIRST semantic in MaxDB.

NULLS are always put at the high end of the sorting list.

This is also documented [here|http://maxdb.sap.com/doc/7_8/45/40635e6c6c4002e10000000a155369/frameset.htm]...

...
Values are compared in accordance with the rules for the comparison predicate.
Within sorting order, NULL values are greater than non-NULL values and special NULL values are greater than non-NULL values but less than NULL values. 
...

So, to get the NULLS before the other rows, you'd have to split your query and provide an additional sorting/grouping column:

select * from (
   select 1 ord, * from null_test where start_date is null
union all
   select 2, * from null_test where start_date is not null 
)
order by ord, start_date asc

cheers,

Lars

[Former Member]

Hi Lars,

Thanks for your answer.

I would not like to use an UNION function.

I'm quite desapointed that we can't have the same behavior with SAP DB and ORACLE (NULL FIRST) adn MS SQL (order by default).

I found this but I don't know if there are some limitations and if it's the good way to do that.

 SELECT * MY_TABLE ORDER BY VALUE(MY_TIMESTAMP,'1000-01-01 00:00:00.0)

Do you have an idea about this?

Cheers,

Nicolas

Edited by: Nicolas DIGUET on Aug 30, 2011 2:49 PM

[lbreddemann]

Hi Nicolas,

I understand that you're disappointed to miss a feature you're used to rely upon.

However, the special handling of NULL values during sorting is extremely seldom used.

In fact, I would bet that most Oracle SQL developers don't even know the "NULLS FIRST/LAST" clause.

And there's a good reason for that: having NULLs in your data usually is not the best thing as you need to write application logic to handle NULL data.

Anyhow, concerning your solution approach: yep, could work.

You may however see performance drawbacks due to the use of the function there.

If you do, you might consider function based indexes for this sorting.

regards,

Lars

[Former Member]

Hi Lars,

Thanks a lot for your help.

Best regards,

Nicolas

[Luke_D]

Nothing like being 13 years late...

order by case isnull(start_date) when 1 then 0 else 1 end), start_date