Ordering in Oracle and SQLServer

Question

Ordering in Oracle and SQLServer

NeoNash

SSC Eights!

Points: 914
More actions
April 22, 2002 at 4:34 pm

#157616

For those working with Oracle and SQLServer:
NULL is in the opposite ends of the result in ORDER BY for Oracle and SQLServer.
Example:
create table test(name varchar(10))
go
insert into test values ('ABC')
go
insert into test values (null)
go
If you do:
select name
from test
order by name
go
the result is:
null
ABC
where as in Oracle, the result would be:
ABC
null (of course a blank here in SQL*Plus!)
Share with me if see any more like this.
Thanks,
NeoNash

Viewing 8 posts - 1 through 7 (of 7 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Oracle I believe is also case sensative by default which I believe plays a part in why nulls are last, but I forget and don't have a server in front of me.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

NeoNash SSC Eights! Points: 914 More actions · Answer 2

You are correct. Oracle is case-sensitive. That is why we have to be careful while developing applications which support both Oracle and SQLserver.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

I forget which one but I believe one of the sort orders in SQL (binary I believe but not 100% sure) will cause SQL to behave in the ordering respect as Oracle including the position nulls output in.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Henrik Staun Poulsen SSCertifiable Points: 6583 More actions · Answer 4

NeoNash,

One of the ways to get the "Null" to appear at the top of the list is like this:

ORDER BY (Coalesce(("2" + Name), "1")

Henrik

NeoNash SSC Eights! Points: 914 More actions · Answer 5

Henrik,

"Null" always appears at the top in SQLserver. If you have a solution to make it appear at the bottom of the list please let me know.

Thanks,

NeoNash

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Hey NeoNash sorry I just don't have a box right now I can reinstall to test which sort order collation may do this but if you want the nulls on the bottom with the rest sorted ascending then do the following

Ex.

SELECT [name] FROM test ORDER BY (CASE WHEN [name] IS NULL THEN 1 ELSE 0 END), [name]

There might be some other ways that I did not think of but I did test.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

NeoNash SSC Eights! Points: 914 More actions · Answer 7

That works but with an addition of DESC

SELECT [name] FROM test ORDER BY (CASE WHEN [name] IS NULL THEN 1 ELSE 0 END) DESC, [name]

Thanks for your help.

NeoNash