Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    5

    Unanswered: How distinct work !!

    Friends,

    This is the sequence of Operation I carried out.

    SQL> Create table x (Name varchar2(20), age number);

    Table Created

    SQL> insert into x values('abc',20);

    1 row created


    SQL> insert into x values('abc',20);

    1 row created

    SQL> insert into x values('efg',20);

    1 row created.

    SQL> insert into x values('hij',20) ;


    1 row created.

    SQL> select distinct name from x where name ='abc';

    NAME
    --------------------
    abc



    SQL> select name,rowid from x where name ='abc';

    NAME ROWID
    -------------------- ------------------
    abc AAAH6MAABAAAPC2AAA
    abc AAAH6MAABAAAPC2AAB


    SQL> select distinct name,age from x where name='abc';

    NAME AGE
    -------------------- ----------
    abc 30


    SQL> select distinct name,rowid from x where name='abc';

    NAME ROWID
    -------------------- ------------------
    abc AAAH6MAABAAAPC2AAA
    abc AAAH6MAABAAAPC2AAB



    My Question is , in the last 2 sections, when I just do a distinct of name and Age I get only one record. While I do a Distinct name and Rowid I get two rows returned. Why is this.

    Thanks in Advance
    Magesh

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Well first off, I dont see you ever inputting 30 into the table...so I'm going to assume 20 = 30.

    ROWID really is an internal thing used by Oracle - in reality, to the developer it doesnt really mean anything. So assuming you entered (ABC, 20) and (ABC, 20) you have 2 rows hence 2 rowids.

    If you select distinct age, you only have one value b/c that is the purpose of distinct - to show you unique values.

    I understand this was just a test, but I hope you would never consider having a table without a primary key! You should never be able to enter (ABC,20) more than once!
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Jul 2004
    Posts
    5
    Yes frnd, as you said, this was just a test and I wanted to know why Oracle behaves in a different manner for Distinct with Some other Column and Distinct with Rowid i.e (DISTINCT NAME, AGE) RETURNS ONLY 1 RECORD while DISTINCT NAME,ROWID returns 2 records. I just trying to figure out how DISTINCT function works. I am wondering if it is dependant on the column we use with Distinct function alone !

    Thx
    Magesh

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    "select DISTINCT x,y,z" returns 1 row per distinct combination of x,y and z. When you introduce ROWID, every combination is distinct because no 2 rows have the same ROWID, by definition.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •