Order by question

How do a construct a select statement that will perform an 'order by ASC',
but will place my nulls at the end of the list instead of the beginning of
the list.
Thanks.
Archerbagman3rd wrote on Wed, 11 May 2005 08:24:01 -0700:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
What's the largest value you expect to see in the column? Place something
larger in the following to replace <<value to use>>
ORDER BY COALESCE(<column>,<<value to use>> )
eg. if column A will have a max value of 100, you would use
ORDER BY COALESCE(A,101)
Dan|||Figure out the "highest" possible value that can be in that column, dependin
g
on what datatype the column is, say it's varchar(6), then it would be
'ZZZZZZ', if it's a smallInt then its 32768, etc.
Then use the Coalesce function
Order By Coalesce(ColNAme, <MaxColValue> )
"bagman3rd" wrote:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
> Thanks.
> Archer|||Use a CASE expression in the ORDER BY clause.
Example:
use northwind
go
select
OrderID,
ShippedDate
from
dbo.orders
order by
case when ShippedDate is null then 1 else 0 end asc,
ShippedDate asc
AMB
"bagman3rd" wrote:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
> Thanks.
> Archer