By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,007 Members | 1,248 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,007 IT Pros & Developers. It's quick & easy.

Order By Nulls First/Last

P: n/a
I just stumbled upon this olap function and wondered why the "NULLS
FIRST/LAST" clause is not possible in normal order by but only using
row_number() over(). Sure, I can do a
SELECT *, row_number() over(ORDER BY col1 ASC NULLS FIRST) AS rn FROM
tab1 ORDER BY rn
to get what I want, but its awfully complicated. (Interestingly, the
optimizer rewrites above order by in an ugly ORDER BY CASE WHEN col1
is NULL THEN 0 ELSE 1, col1)

Anyone could shed any light on this? Will the functionality maybe
available in future versions of DB2 (or maybe it is already, just not
in LUW)?

Regards,
Janick

Feb 4 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Janick Bernet wrote:
I just stumbled upon this olap function and wondered why the "NULLS
FIRST/LAST" clause is not possible in normal order by but only using
row_number() over(). Sure, I can do a
SELECT *, row_number() over(ORDER BY col1 ASC NULLS FIRST) AS rn FROM
tab1 ORDER BY rn
to get what I want, but its awfully complicated. (Interestingly, the
optimizer rewrites above order by in an ugly ORDER BY CASE WHEN col1
is NULL THEN 0 ELSE 1, col1)

Anyone could shed any light on this? Will the functionality maybe
available in future versions of DB2 (or maybe it is already, just not
in LUW)?
I don't think it has come up as a request (at least I have never heard
of it). OLAP was added only in DB2 V7.1. Which is why it obtained more
ORDER BY options.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 4 '07 #2

P: n/a
>Anyone could shed any light on this? Will the functionality maybe available in future versions of DB2 (or maybe it is already, just not in LUW)? <<

It is part of the SQL-2003 specs for OLAP extensions, so there is a
good chance you will have it soon. The sort order of the NULLs was
originally implementation defined in the Standards.

Feb 5 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.