Archives of the TeradataForum

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

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

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>>