Ordering with nulls

Question

Post reply

Ordering with nulls

kuopaz

Default port

Points: 1449
More actions
August 31, 2011 at 12:52 pm

#242635

I've the results of a full outer join that I want ordered. This is just an example as I'm struggling to get my head around it.
create table TestTable
(
ColA nvarchar(50) null,
ColB nvarchar(50) null
)
insert TestTable (ColA, ColB)
select 'W', 'W'
union all
select 'X', 'W'
union all
select 'Y', 'Y'
union all
select 'Z', 'Z'
union all
select null, 'W'
union all
select null, 'X'
union all
select null, 'Y'
union all
select null, 'Z'
union all
select 'W', null
union all
select 'X', null
union all
select 'Y', null
union all
select 'Z', null
I want it ordered with all the Ws together etc. with no nulls first, followed by nulls in ColA and then nulls in ColB:
WW
NULLW
WNULL
XX
NULLX
XNULL
YY
NULLY
YNULL
ZZ
NULLZ
ZNULL

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

How about this ?

select ColA, ColB

from TestTable

order by isnull( ColA , ColB) , isnull( ColB ,ColA )

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

This will be more robust:

select ColA, ColB

from TestTable

order by isnull( ColA , ColB) ,

case when ColA is not null and ColB is not null then 1

when ColA is null then 2

when ColB is null then 3

end

toddasd SSCertifiable Points: 6298 More actions · Answer 3

Nice shot, CC. You got there seconds before me 😉

select * from TestTable

order by

ISNULL(ColA, ColB) + ISNULL(ColB, ColA)

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

toddasd (8/31/2011)
Nice shot, CC. You got there seconds before me 😉

😀

toddasd SSCertifiable Points: 6298 More actions · Answer 5

ColdCoffee (8/31/2011)
This will be more robust:
select ColA, ColB
from TestTable
order by isnull( ColA , ColB) ,
case when ColA is not null and ColB is not null then 1
when ColA is null then 2
when ColB is null then 3
end

Not just robust, but correct. The other solutions are not in the right order.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

kuopaz Default port Points: 1449 More actions · Answer 6

That seems to do it. I'd experimented with case and isnull but never got as far as using them using together. Thanks for this!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

toddasd (8/31/2011)
ColdCoffee (8/31/2011)
This will be more robust:
select ColA, ColB
from TestTable
order by isnull( ColA , ColB) ,
case when ColA is not null and ColB is not null then 1
when ColA is null then 2
when ColB is null then 3
end
Not just robust, but correct. The other solutions are not in the right order.

YEah, the first query we both posted gave ordered result, but on large sets, we could expect mis-orders. This one will nail it.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

zapouk (8/31/2011)
That seems to do it. I'd experimented with case and isnull but never got as far as using them using together. Thanks for this!

You're welcome. Thanks for setting up the questions; i dint take the pain of creating sample data, insert into tables blah blah.. so pat on your shoulder for a neatly-presented question.

toddasd SSCertifiable Points: 6298 More actions · Answer 9

Seconded. A good setup gets your question answered quickly. With tested code! 😎

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.