SQL Query Help-- Order By Clause

[Muzamil]

Hi

I want a simple select query on a column-name (smalldatetime) with
values dislayed in desc order with null values FIRST.

i.e.

Select orderdate from orders
order by ( null values first and then orderdate in desc order)

could any one please help

Thanks

[me]

Just an idea that might work somehow don't know if its a good way to do it
or not but ...
order by
isnull(cast(dat efieldname as varchar(20)),'Z ') desc


"Muzamil" <mu*****@hotmai l.com> wrote in message
news:5a******** *************** ***@posting.goo gle.com...

Hi

I want a simple select query on a column-name (smalldatetime) with
values dislayed in desc order with null values FIRST.

i.e.

Select orderdate from orders
order by ( null values first and then orderdate in desc order)

could any one please help

Thanks

[Hugo Kornelis]

On 12 Aug 2004 07:09:16 -0700, Muzamil wrote:

Hi

I want a simple select query on a column-name (smalldatetime) with
values dislayed in desc order with null values FIRST.

i.e.

Select orderdate from orders
order by ( null values first and then orderdate in desc order)

could any one please help

Thanks

Hi Muzamil,

ORDER BY
CASE WHEN orderdate IS NULL THEN 1 ELSE 2 END,
orderdate DESC

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Garry]

>

ORDER BY
CASE WHEN orderdate IS NULL THEN 1 ELSE 2 END,
orderdate DESC

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

IMHO - much better

declare @a table (
tid int identity(1,1) NOT NULL PRIMARY KEY
,tdate datetime NULL
)
insert into @a (
tdate
) select
'20040901'
union all
select
'20040601'
union all
select
'20040901'
union all
select
'20030101'
union all
select
NULL
union all
select
'20040212'
union all
select
NULL
select * from @a
ORDER BY
ISNULL(tdate, '20500101') desc

[Hugo Kornelis]

On Thu, 12 Aug 2004 20:02:56 +0300, Garry wrote:

ORDER BY
CASE WHEN orderdate IS NULL THEN 1 ELSE 2 END,
orderdate DESC

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

IMHO - much better

declare @a table (
tid int identity(1,1) NOT NULL PRIMARY KEY
,tdate datetime NULL
)
insert into @a (
tdate
) select
'20040901'
union all
select
'20040601'
union all
select
'20040901'
union all
select
'20030101'
union all
select
NULL
union all
select
'20040212'
union all
select
NULL
select * from @a
ORDER BY
ISNULL(tdate, '20500101') desc

Hi Garry,

Yes you're right, that's better. Thanks. Though I'd use a date further in
the future (99991231 - the maximum datetime). Dates after Jan 1st 2050
might already start appearing in some databases (e.g. as ending date for a
mortgage or life insurance policy).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Garry]

Thank you, Hugo.
I did not know, that datetime type is such large.
Now I have read BOL.

[Erland Sommarskog]

Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:

Yes you're right, that's better. Thanks. Though I'd use a date further in
the future (99991231 - the maximum datetime). Dates after Jan 1st 2050
might already start appearing in some databases (e.g. as ending date for a
mortgage or life insurance policy).

We went through a period where we tried to save some space by using
smalldatetime which goes over the brink in June 2076. We later reverted,
but a few tables were left behind (and an even smaller few may still
remain). And then one day as a customer of ours tried to import
securities into our database, thing blew up beacuse of a bond with a due
date in 2083 which came past this particular table.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Muzamil]

Thanks everyone for the solution as well as the valuable information

Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>...

Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:

Yes you're right, that's better. Thanks. Though I'd use a date further in
the future (99991231 - the maximum datetime). Dates after Jan 1st 2050
might already start appearing in some databases (e.g. as ending date for a
mortgage or life insurance policy).

We went through a period where we tried to save some space by using
smalldatetime which goes over the brink in June 2076. We later reverted,
but a few tables were left behind (and an even smaller few may still
remain). And then one day as a customer of ours tried to import
securities into our database, thing blew up beacuse of a bond with a due
date in 2083 which came past this particular table.