1 2 Previous Next 22 Replies Latest reply on Mar 19, 2013 12:30 PM by 793996

    Distinct Behaviour

    793996
      Greetings of the day to All,

      I'm using Oracle 11g R2. Do we have some specific/predictable order in which column values are returned when distinct is applied?
      My basic data somehow looks as follows:
      WITH TEST AS
           (SELECT 'atemp@test.com' email
              FROM DUAL
            UNION ALL
            SELECT 'xtemp@test.com' email
              FROM DUAL
            UNION ALL
            SELECT 'ctemp@test.com' email
              FROM DUAL
            UNION ALL
            SELECT 'dtemp@test.com' email
              FROM DUAL
            UNION ALL
            SELECT 'btemp@test.com' email
              FROM DUAL
            UNION ALL
            SELECT 'btemp@test.com' email
              FROM DUAL
            UNION ALL
            SELECT 'dtemp@test.com' email
              FROM DUAL
            UNION ALL
            SELECT 'atemp@test.com' email
              FROM DUAL
            UNION ALL
            SELECT 'etemp@test.com' email
              FROM DUAL
            UNION ALL
            SELECT 'xtemp@test.com' email
              FROM DUAL
            UNION ALL
            SELECT 'ztemp@test.com' email
              FROM DUAL
            UNION ALL
            SELECT 'ytemp@test.com' email
              FROM DUAL)
      SELECT DISTINCT email
                 FROM TEST
      But data order of the resultset returned by it is mess.

      Thanks,
      Vivek
        • 1. Re: Distinct Behaviour
          jeneesh
          Vivek wrote:
          Greetings of the day to All,

          I'm using Oracle 11g R2. Do we have some specific/predictable order in which column values are returned when distinct is applied?
          No..

          To make the result set in order, you need to use ORDER BY. And that IS THE SIMPLEST way ..

          What is the difficulty you are facing in giving an ORDER BY?

          Means, why cant you add
          order by email
          • 2. Re: Distinct Behaviour
            BEDE
            I think the order by clause is what you need. And I don't believe there is any other method.
            • 3. Re: Distinct Behaviour
              793996
              I thought of using it (ORDER BY) but per requirement they want the list where all the non-first occurance of the data has to be removed without hampering the actual order.

              Well i've did it this way.
              WITH TEST AS
                   (SELECT 'atemp@test.com' email
                      FROM DUAL
                    UNION ALL
                    SELECT 'xtemp@test.com' email
                      FROM DUAL
                    UNION ALL
                    SELECT 'ctemp@test.com' email
                      FROM DUAL
                    UNION ALL
                    SELECT 'dtemp@test.com' email
                      FROM DUAL
                    UNION ALL
                    SELECT 'btemp@test.com' email
                      FROM DUAL
                    UNION ALL
                    SELECT 'btemp@test.com' email
                      FROM DUAL
                    UNION ALL
                    SELECT 'dtemp@test.com' email
                      FROM DUAL
                    UNION ALL
                    SELECT 'atemp@test.com' email
                      FROM DUAL
                    UNION ALL
                    SELECT 'etemp@test.com' email
                      FROM DUAL
                    UNION ALL
                    SELECT 'xtemp@test.com' email
                      FROM DUAL
                    UNION ALL
                    SELECT 'ztemp@test.com' email
                      FROM DUAL
                    UNION ALL
                    SELECT 'ytemp@test.com' email
                      FROM DUAL)
              SELECT   email
                  FROM (SELECT ROWNUM rn, email,
                               ROW_NUMBER () OVER (PARTITION BY email ORDER BY email) rn1
                          FROM TEST)
                 WHERE rn1 = 1
              ORDER BY rn
              Thanks,
              Vivek
              • 4. Re: Distinct Behaviour
                Vivek L
                Vivek wrote:
                I thought of using it (ORDER BY) but per requirement they want the list where all the non-first occurance of the data has to be removed without hampering the actual order.
                Looking at the sample data, it is difficult to ascertain if the rows are sorted in any logical manner.
                What is the sorting logic applied to get the source rows in actual order ?
                • 5. Re: Distinct Behaviour
                  Karthick2003
                  Vivek wrote:
                  I thought of using it (ORDER BY) but per requirement they want the list where all the non-first occurance of the data has to be removed without hampering the actual order.
                  How the actual order is being defined? You need to have a column to define the order of the rows in a table. A data in table (Heap organized) does not have any order of storage.
                  SELECT   email
                      FROM (SELECT ROWNUM rn, email,
                                   ROW_NUMBER () OVER (PARTITION BY email ORDER BY email) rn1
                              FROM TEST)
                     WHERE rn1 = 1
                  ORDER BY rn
                  Above code assumes that the data will be returned in some order. But oracle does not give you any assurance for that.
                  • 6. Re: Distinct Behaviour
                    jeneesh
                    What is the non-first occurrence?


                    How can you define first and last if there is no ORDER?

                    I would like to use DISTINCT with ORDER BY.

                    But, your approach, using ROW_NUMBER also looks good.

                    Are you facing any issues with this?
                    • 7. Re: Distinct Behaviour
                      Manik
                      WITHOUT HAMPERING the order ..huh...

                      OK forum members I am not trying to abuse IOT here,(using this just for the requirement of OP) A unique feature of IOT is that it returns the order of rows exactly as they were inserted. (Correct me if this is not the case)

                      Sample given below:
                      --------------------------------

                      CREATE TABLE sample_iot
                      (
                         id      NUMBER PRIMARY KEY,
                         email   VARCHAR2 (100)
                      )
                      ORGANIZATION INDEX;
                      
                      ---- ID is optional 
                      
                      insert into sample_iot 
                      WITH TEST AS
                           (SELECT 'atemp@test.com' email
                              FROM DUAL
                            UNION ALL
                            SELECT 'xtemp@test.com' email
                              FROM DUAL
                            UNION ALL
                            SELECT 'ctemp@test.com' email
                              FROM DUAL
                            UNION ALL
                            SELECT 'dtemp@test.com' email
                              FROM DUAL
                            UNION ALL
                            SELECT 'btemp@test.com' email
                              FROM DUAL
                            UNION ALL
                            SELECT 'btemp@test.com' email
                              FROM DUAL
                            UNION ALL
                            SELECT 'dtemp@test.com' email
                              FROM DUAL
                            UNION ALL
                            SELECT 'atemp@test.com' email
                              FROM DUAL
                            UNION ALL
                            SELECT 'etemp@test.com' email
                              FROM DUAL
                            UNION ALL
                            SELECT 'xtemp@test.com' email
                              FROM DUAL
                            UNION ALL
                            SELECT 'ztemp@test.com' email
                              FROM DUAL
                            UNION ALL
                            SELECT 'ytemp@test.com' email
                              FROM DUAL)
                      select rownum,test.* from test;
                      
                      commit;
                      select * from sample_iot;   ---- Without any order  by clause
                      OUTPUT:
                      ID     EMAIL
                      --------------------------------------
                      1     atemp@test.com
                      2     xtemp@test.com
                      3     ctemp@test.com
                      4     dtemp@test.com
                      5     btemp@test.com
                      6     btemp@test.com
                      7     dtemp@test.com
                      8     atemp@test.com
                      9     etemp@test.com
                      10     xtemp@test.com
                      11     ztemp@test.com
                      12     ytemp@test.com
                      Cheers,
                      Manik.
                      • 8. Re: Distinct Behaviour
                        jeneesh
                        Manik wrote:
                        WITHOUT HAMPERING the order ..huh...

                        A unique feature of IOT is that it returns the order of rows exactly as they were inserted. (Correct me if this is not the case)
                        That is not correct..

                        The data will be ordered based on the primary key, not on the order of insertion..
                        • 9. Re: Distinct Behaviour
                          Manik
                          Hey Jeenesh thx for correcting me.

                          Yes, you are right... just read that.

                          http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables012.htm

                          Just thinking how OP can get this done without separate column (as I used primary key)....

                          I guess even rowid cannot be used here. :(

                          Cheers,
                          Manik.
                          • 10. Re: Distinct Behaviour
                            Hemant K Chitale
                            without hampering the actual order.
                            You may think that there is some "actual order" in the data, but, by definition, Oracle doesn't maintain any order in a Heap Table. There is never a guarantee that you will retrieve data in a specific order unless you specify an ORDER BY clause.


                            Hemant K Chitale
                            • 11. Re: Distinct Behaviour
                              jeneesh
                              Manik wrote:
                              Just thinking how OP can get this done without separate column (as I used primary key)....
                              There is no question of IOT here..

                              The OP cant get data in the order of insertion without ORDER BY..

                              If he want to order the data based on insertion time, he need to have a column like CREATION_DATE, and use it in the ORDER BY clause..
                              • 12. Re: Distinct Behaviour
                                Manik
                                Ok fine :)

                                Cheers,
                                Manik.
                                • 13. Re: Distinct Behaviour
                                  Paul  Horth
                                  Manik,

                                  The only way of guaranteeing order is to use ORDER BY. Doesn't matter whether it is IOT or not.

                                  (Hint, think what would happen if the DBA decided to make the table parallel 4)
                                  • 14. Re: Distinct Behaviour
                                    Manik
                                    Yes correct.. I got your point.

                                    Cheers,
                                    Manik.
                                    1 2 Previous Next