Problems ordering NULL First - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problems ordering NULL First

Author

Topic

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-24 : 06:29:17

Hi all,

I am creating a report consisting of a union of 2 resultsets.
Basically I am trying to this in the report:

create table table1( startdate datetime not null )
create table table2( startdate datetime null )

insert table1 select '20040801' union select '20040822'
insert table2 select null

select startdate from table1
union all
select startdate from table2
order by startdate desc

--drop table table1
--drop table table2

-- Want this !
startdate
----------------------------------------
NULL
2004-08-22 00:00:00.000
2004-08-01 00:00:00.000

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-24 : 06:41:59

select * from (
select startdate from table1
union all
select startdate from table2
) b
ORDER BY CASE WHEN startdate is null then 1 else 2 END, startdate

(Bit messy because of the union)

-------
Moo. :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-24 : 06:59:57

this does not return the correct result to me.

this does:
create table table1( startdate datetime not null )
create table table2( startdate datetime null )

insert table1 select '20040801' union select '20040822'
insert table2 select null

select 1 as sort, startdate
into #temp
from table2

insert #temp
select 2, startdate
from table1
order by startdate desc

select startdate from #temp order by sort, startdate desc

drop table table1
drop table table2
drop table #temp

Go with the flow & have fun! Else fight the flow :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-24 : 07:01:40

mr_mist: your solution works ok :) just needs startdate DESC at the end :)))

Go with the flow & have fun! Else fight the flow :)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-24 : 07:30:25

Yup, nice solution, Thanx mr_mist!

/rockmoose

a_shipra
Starting Member

20 Posts

Posted - 2004-08-24 : 21:01:53

select * from (
select startdate from table1
union all
select startdate from table2
) b
ORDER BY CASE WHEN startdate is null then 1 else 2 END, startdate

ORDER BY CASE WHEN startdate is null then 1 else 2 END,startdate ?
can you tell me what is this statement doing. what do you mean by 1 and 2

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-25 : 00:18:28

if startdate is null order by first column other by order by socond column.

Example:-

if you have table student (id int,name varchar(20),address varchar(100))

select * from student order by Name
will be same as
select * from student order by 2

Cheers

mk_garg

a_shipra
Starting Member

20 Posts

Posted - 2004-08-25 : 00:32:10

Thanks mk_garg .

but in the above query there was no 2nd column.
so why we are saying 1 or 2

Sorry if i sound stupid.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-25 : 01:23:06

No, that is not ordering by two different columns, it's order by two different values. Actually we have discussed this on the forums a few times before, this is probably best classified as unexpected behaviour on the part of SQL Server, but it is useful! What that statement does it order the resultset by the results of that expression. All the expression says is - if the date column is null order it by value 1, otherwise use 2. Think of it as another phantom column in the resultset which contains either 1 or 2. All the rows with startdate NULL will have the value 1 in the column and the others will have 2 in it. This means all rows with a null startdate float to the top of the resultset.

Interestingly, if you substitute column names instead of numbers, it works as mk_garg had explained it would - sorting by values in one column or the other.

OS

a_shipra
Starting Member

20 Posts

Posted - 2004-08-25 : 02:31:48

hi OS,

so you want to say here that instead of ordering it on the startdate it is ordering on the other column that .Am i right?

if yes then even when we say desc order then also null is coming top why?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-25 : 02:56:58

quote:

posted by a_shipre
ORDER BY CASE WHEN startdate is null then 1 else 2 END,startdate ?
can you tell me what is this statement doing. what do you mean by 1 and 2

SqlServer first orders by the expression "CASE WHEN startdate is null then 1 else 2 END"
This will put all the rows with null startdate on top.
Then SqlServer orders by startdate [asc/desc]
this will then order the rows by startdate.
( n.b. the second ordering will not reorder the first ordering which put the rows with null at the top )

You might call this a little "trick" or smthng.

/rockmoose

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-25 : 02:59:20

quote:
Originally posted by a_shipra

hi OS,

so you want to say here that instead of ordering it on the startdate it is ordering on the other column that .Am i right?

Sort of. It's ordering like this

NULL = 1
anything other than NULL = 2

Since 1 is higher than 2, the NULLs go first.

quote:

if yes then even when we say desc order then also null is coming top why?

Because the DESC only applies to the startdate parameter, not the whole ORDER BY. (the 1 and 2 bit is ASC, but that is implied.)

The (full) code is actually -

ORDER BY CASE WHEN startdate is null then 1 else 2 END ASC , startdate DESC

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources