Home » SQL & PL/SQL » SQL & PL/SQL » How ordering works with NULL vaalue
How ordering works with NULL vaalue [message #226848] Mon, 26 March 2007 15:01 Go to next message
yogeshse
Messages: 11
Registered: December 2005
Location: Chennai
Junior Member
Hi,

I've table X with one column y. It has these values - 1,2,3,null

if I select y from X order by y desc & then Asc, what would be the output ?? and why is that ?? (here i want to know how null is used in comparision) scripts are here --

create table X (y number);
/
insert into X values(1);
insert into X values(2);
insert into X values(3);
insert into X values(null);
/
select y from x order by y asc;
/
select y from x order by y desc;
/

Thanks for any help !!
Re: How ordering works with NULL vaalue [message #226855 is a reply to message #226848] Mon, 26 March 2007 15:11 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
yogeshse wrote on Mon, 26 March 2007 16:01
Hi,

I've table X with one column y. It has these values - 1,2,3,null

if I select y from X order by y desc & then Asc, what would be the output ?? and why is that ?? (here i want to know how null is used in comparision) scripts are here --

create table X (y number);
/
insert into X values(1);
insert into X values(2);
insert into X values(3);
insert into X values(null);
/
select y from x order by y asc;
/
select y from x order by y desc;
/

Thanks for any help !!


Are you really asking what would be the output? Do you not have Oracle yourself so you can type the command yourself? A little effort please.

This newbie question has been moved.
Re: How ordering works with NULL vaalue [message #226862 is a reply to message #226848] Mon, 26 March 2007 15:44 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Oracle documentation is your friend - you should always consult it.
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2171079

Gints Plivna
http://www.gplivna.eu
Re: How ordering works with NULL vaalue [message #227347 is a reply to message #226862] Wed, 28 March 2007 01:35 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
in descending order first null then lower value
and in ascending order first higher value and last null
Re: How ordering works with NULL vaalue [message #227364 is a reply to message #227347] Wed, 28 March 2007 01:52 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
muzahidul islam wrote on Wed, 28 March 2007 08:35
in descending order first null then lower value
and in ascending order first higher value and last null


By default: yes.

from the SQL Reference
Oracle sorts nulls following all others in ascending order and preceding all others in descending order.
<snip>
NULLS FIRST | NULLS LAST

Specify whether returned rows containing null values should appear first or last in the ordering sequence.

NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.


You can override it, though:

set NULL 'null'

create table X (y number)
/
insert into X values(1);
insert into X values(2);
insert into X values(3);
insert into X values(null);


PROMPT order by y asc
select y from x order by y asc
/
PROMPT order by y desc
select y from x order by y desc
/

PROMPT order by y asc NULLS FIRST
select y from x order by y asc NULLS FIRST
/
PROMPT order by y desc NULLS FIRST
select y from x order by y desc NULLS FIRST
/

PROMPT order by y asc NULLS LAST
select y from x order by y asc NULLS LAST
/
PROMPT order by y desc NULLS LAST
select y from x order by y desc NULLS LAST
/

drop table x
/


My test:
SQL> @orafaq

Table created.


1 row created.


1 row created.


1 row created.


1 row created.

order by y asc

         Y
----------
         1
         2
         3
null

order by y desc

         Y
----------
null
         3
         2
         1

order by y asc NULLS FIRST

         Y
----------
null
         1
         2
         3

order by y desc NULLS FIRST

         Y
----------
null
         3
         2
         1

order by y asc NULLS LAST

         Y
----------
         1
         2
         3
null

order by y desc NULLS LAST

         Y
----------
         3
         2
         1
null


Table dropped.


MHE

[Updated on: Wed, 28 March 2007 01:53]

Report message to a moderator

Previous Topic: limited input choices for fields
Next Topic: No logging?
Goto Forum:
  


Current Time: Sun Dec 11 02:05:41 CST 2016

Total time taken to generate the page: 0.17061 seconds