Comments below
"Chris Brady" <chrisjbrady_at_yahoo.com> wrote in message
news:51604466.0110290314.226d2546_at_posting.google.com...
> Please can someone explain this? Its the same table compared with
> itself. The first case I can understand but why is the second case
> giving the same total? I would have thought that it would be zero.
<snip>
> SQL> select count(distinct(i.myfield)) from temp_data i, temp_data w
> where i.myfield <> w.myfield;
>
> COUNT(DISTINCT(I.MYFIELD))
> --------------------------
> 47548
Your table has 47548 distinct values of myfield. Each of those is clearly
not equal to the other 47547 values in the table. Thus each value of myfield
will satisfy your where clause at least 47547 times. Assuming myfield is not
in fact unique try taking the distinct keyword out of the two pieces of sql
to see the results more clearly.
--
Niall Litchfield
Oracle DBA
Audit Commission Uk
Received on Mon Oct 29 2001 - 05:29:57 CST
