Archives of the TeradataForum
Message Posted: Fri, 29 Jul 2005 @ 15:35:11 GMT
Subj: | | Re: NULLS LAST and NULLS FIRST |
|
From: | | Dieter Noeth |
Aananth.S wrote:
| There is a NULLS LAST and NULLS FIRST clause available in Oralce which can be used in the ordering of nulls - both in normal order bys and
in windowing functions. | |
NULLS LAST|FIRST is part of SQL:2003, probably suggested by Oracle ;-)
| Is such a facility availabe in TD? | |
No, but would be nice as an Enhancement Request.
| or do we need to use coalesce as a workaround. I couldnt find any in the documentation at least! | |
Teradata sorts NULL as lowest value:
order by col asc NULLS FIRST
-> order by col asc
order by col desc NULLS LAST
-> order by col desc
Instead of thinking about the right value to use in coalesce you can calculate an extra column:
order by col asc NULLS LAST
-> order by case when col is null then 1 else 0 end, col asc
order by col desc NULLS FIRST
-> order by case when col is null then 0 else 1 end, col desc
Of course the extra sort column will need more spool space...
Dieter
|