Home » SQL & PL/SQL » SQL & PL/SQL » Distinct with NULL
Distinct with NULL [message #395266] Tue, 31 March 2009 23:59 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
SELECT comm FROM emp;
comm
---------
500
400
300
300
NULL
NULL
NULL

SELECT DISTINCT comm FROM emp;
comm
----------
500
400
300
NULL
According to nulls concept any null is not equal to other null value.But why it's returning only one null value.Please give me the reason it's very urgent.



[MERGED by LF]

[Updated on: Tue, 21 April 2009 14:15] by Moderator

Report message to a moderator

Re: distinct [message #395268 is a reply to message #395266] Wed, 01 April 2009 00:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Please give me the reason it's very urgent.
Please explain why it is URGENT for me to solve this problem for you?

If NULL <> NULL, how do you DISTINCT NULLs?

Re: distinct [message #395270 is a reply to message #395268] Wed, 01 April 2009 00:12 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
are you saying null=null .
Re: distinct [message #395272 is a reply to message #395266] Wed, 01 April 2009 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>are you saying null=null
NEVER!
Re: distinct [message #395297 is a reply to message #395266] Wed, 01 April 2009 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The reason is that it is the choice ISO made.

Regards
Michel
distinct with null [message #399193 is a reply to message #395266] Tue, 21 April 2009 01:39 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Employee table contains the following data

SQL>Select * from employee;
SALARY
----------
100
100
200
200



7 rows selected

In the employee table last three values are nul values

SQL> select distinct salary from employee;

SALARY
----------------
100
200

3 rows selected.

According to nuls concept any Null is not equal to NULL,but why it's returning only one NULL value.can u tellme the reason.
Re: distinct with null [message #399202 is a reply to message #399193] Tue, 21 April 2009 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Because it is how it is defined, designed and implemented.

Regards
Michel
Re: distinct with null [message #399305 is a reply to message #399193] Tue, 21 April 2009 08:57 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Why do you think this would have changed over the last 20 days?
Or did you forget you posted this same question not even 3 weeks ago?!
Previous Topic: shuffling of data
Next Topic: Addition date and varchar2
Goto Forum:
  


Current Time: Thu Feb 06 14:34:19 CST 2025