Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 08 Jan 2004 @ 15:51:29 GMT


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


Subj:   Re: The ORDER BY clause
 
From:   Jim Downey

I am not sure if I understand your question correctly.

I tried the following:

We have a small table with an id column and a year.

sel   *
from  NDW.AccountingYear
order by    1
;

this also works:

sel   *
from  NDW.AccountingYear
order by    AccountingYear_Id
;

Where Accounting Year_id happens to be the first column.

You can then use the case statement to do your order by conditions as you described in your note. See my example d) to get a more useful result.

a)

sel   *
from  NDW.AccountingYear
order by  (Case when AccountingYear_Id is NULL then 0 else 1 end)
;

b)

sel   *
from  NDW.AccountingYear
order by  (Case when AccountingYear_Id <=0 then 0 else 1 end)
;

c)

sel   *
from  NDW.AccountingYear
order by  (Case when AccountingYear_Id >4 then 0 else 1 end)
;

d) Your example worked as you had coded it. (1,2,1) but you can combine the two and get:

sel   *
from  NDW.AccountingYear
order by  (Case when AccountingYear_Id >2000 then 0 else 1 end),1
;

This brought all the years after 2000 to the top and sorted them first in order followed by the years less than 2000 in order.


Jim



     
  <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: 27 Dec 2016