Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 29 Jul 2005 @ 15:35:11 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


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



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023