11 Replies Latest reply: Jan 2, 2013 2:40 AM by Nicosa-Oracle RSS

    Distinct Function

    976439
      Hi, i was looking to find out if it is possible to apply the distinct function in a query to only certain fields?

      If somebody could please advise...

      I dont have an example or table so apologies just looking to find out, i no that if i add 'select distinct.. than this would apply unique values to all fields within the select statement, but is it possible to just apply it to 1 or 2 fields?

      Thanks in advance.
        • 1. Re: Distinct Function
          ranit B
          973436 wrote:
          Hi, i was looking to find out if it is possible to apply the distinct function in a query to only certain fields?

          If somebody could please advise...

          I dont have an example or table so apologies just looking to find out, i no that if i add 'select distinct.. than this would apply unique values to all fields within the select statement, but is it possible to just apply it to 1 or 2 fields?

          Thanks in advance.
          I'm not getting your doubt properly... You want have few specific cols where you need to apply DISTINCT right??
          Do you want something like -
          --- select distinct * 
          /* instead do */
          select distinct col_1,col_2
          from table_x;
          Edited by: ranit B on Dec 20, 2012 4:54 PM
          • 2. Re: Distinct Function
            hitgon
            when you use the DISTINCT with only single column

            SELECT DISTINCT CODE FROM ACCOUNT;

            Result row contain only the distinct/unique CODE as sql query result

            when you use the DISTINCT with the more then one column

            SELECT DISTINCT CODE,BRANCH,TYPE,ACNO FROM ACCOUNT;

            Here the result contain the distinct/unique rows of CODE,BRANCH,TYPE,ACNO
            combination of CODE,BRANCH,TYPE,ACNO is distinct
            • 3. Re: Distinct Function
              pollywog
              not exactly sure what you are after but you can use the row_number or rank function to see if there is more than one entry of the same type in a field/s.
              /* Formatted on 12/20/2012 6:44:56 AM (QP5 v5.185.11230.41888) */
              WITH sample_data
                   AS (SELECT 'A' col1, 'B' col2, 'C' col3 FROM DUAL
                       UNION ALL
                       SELECT 'D', 'E', 'F' FROM DUAL
                       UNION ALL
                       SELECT 'G', 'H', 'C' FROM DUAL
                       UNION ALL
                       SELECT 'I', 'J', 'K' FROM DUAL
                       UNION ALL
                       SELECT 'L', 'M', 'F' FROM DUAL)
              SELECT sample_data.*,
                     ROW_NUMBER () OVER (PARTITION BY col3 ORDER BY NULL) rn,
                     DECODE (ROW_NUMBER () OVER (PARTITION BY col3 ORDER BY NULL),
                             1, NULL,
                             'duplicate value in col3')
                        is_dup
                FROM sample_data
              COL1     COL2     COL3     RN     IS_DUP
              A     B     C     1     
              G     H     C     2     duplicate value in col3
              L     M     F     1     
              D     E     F     2     duplicate value in col3
              I     J     K     1     
              • 4. Re: Distinct Function
                976439
                Thanks guys, I was just trying to work something out to replicate the remove duplicates from Excel, as this allows you to select specific fields so was looking to find out if there was anything similar in SQL.

                Thanks for all your help and examples
                • 5. Re: Distinct Function
                  Nicosa-Oracle
                  Tell us, what your "select distinc(c1,c2), c3 from test" should return if t haves the following rows :
                  C1 | C2 | C3
                  -------------
                   1 |  1 |  1
                   1 |  1 |  2
                   1 |  1 |  3
                   1 |  2 |  3
                   1 |  2 |  4
                   1 |  2 |  5
                  But if you tell us what you're trying to achieve we might help.
                  • 6. Re: Distinct Function
                    976439
                    Nicosa thanks for your response. I think I maybe able to explain a little more clearer as I have an example that I am stuck with at the mo.

                    I currently have the following query:
                    SELECT distinct tbl1.Order_Num, tbl1.Tel_No, tbl2.user_id, tbl2.name, tbl2.date_time, tbl1.Product, tbl1.Company_Name
                    FROM tbl2 INNER JOIN tbl1 ON tbl2.ctel_no = tbl1.Tel_No
                    This works but the problem is that I dont get distinct values because the date_time field has different times for all users. The following is an example of my resultset:
                    Order_No | Tel_No | user_id | name |       date_time        | product | company_name
                    -------------    ---------    ---------    -------   ---------------------------    ----------    ---------------------
                    1234          5678      12         ABC     10-DEC-12 17:01:21   DF          HUMPTY
                    1234          5678      12         ABC     10-DEC-12 16:22:11   DF          HUMPTY
                    1234          5678      12         ABC     10-DEC-12 16:52:28   DF          HUMPTY
                    1234          5678      12         ABC     10-DEC-12 17:46:15   DF          HUMPTY
                    So what I would like is to check the first four columns (order_no, tel_no, user_id and name) and if this have any duplicates than remove these even though the date_time values are not distinct. So this should leave me with the following only.
                    Order_No | Tel_No | user_id | name |       date_time        | product | company_name
                    -------------    ---------    ---------    -------   ---------------------------    ----------    ---------------------
                    1234          5678      12         ABC     10-DEC-12 17:01:21   DF          HUMPTY
                    Hopefully this clarifies what I am looking to do. i dont know if another option would be to split the date and time field but again I would still require distinct across a few fields and not include the time field.

                    An example of what I am trying to do can be found in Excel and this is the remove duplicates function in Excel and thats what I am trying to replicate within SQL, as the query that i am trying to write is based on a file that was put together in Excel.

                    If somebody could please help or advice if this can be done.

                    Thanks in advance.

                    Edited by: 973436 on 24-Dec-2012 07:38
                    • 7. Re: Distinct Function
                      chris227
                      SELECT 
                        tbl1.Order_Num
                      , tbl1.Tel_No
                      , tbl2.user_id
                      , tbl2.name
                      , max(tbl2.date_time) date_time
                      , tbl1.Product
                      , tbl1.Company_Name
                      FROM
                       tbl2
                      INNER JOIN
                       tbl1
                      ON tbl2.ctel_no = tbl1.Tel_No
                      group by
                        tbl1.Order_Num
                      , tbl1.Tel_No
                      , tbl2.user_id
                      , tbl2.name
                      , trunc(tbl2.date_time) -- to group just by day
                      , tbl1.Product
                      , tbl1.Company_Name
                      You should think about what date you want to retrieve for the single row. In this case the last date is taken.

                      Edited by: chris227 on 24.12.2012 07:42
                      • 8. Re: Distinct Function
                        Warren Tolentino
                        if i correctly understand your posting that you want to returns distinct rows which has the first date_time. you can try to use this example below. the example is using an analytic query.
                        select vTab.order_num,
                               vTab.tel_no,
                               vTab.user_id,
                               vTab.name,
                               vTab.date_time,
                               vTab.product,
                               vTab.copmpany_name
                          from (SELECT distinct 
                                       tbl1.Order_Num, 
                                       tbl1.Tel_No, 
                                       tbl2.user_id, 
                                       tbl2.name, 
                                       tbl2.date_time, 
                                       tbl1.Product, 
                                       tbl1.Company_Name,
                                       row_number() over (partition by tbl1.Order_Num, 
                                                                       tbl1.Tel_No, 
                                                                       tbl2.user_id, 
                                                                       tbl2.name, 
                                                                       tbl2.date_time
                                                          order by tbl1.Order_Num, 
                                                                   tbl1.Tel_No, 
                                                                   tbl2.user_id, 
                                                                   tbl2.name, 
                                                                   tbl2.date_time) rn
                                   FROM tbl2 INNER JOIN tbl1 ON tbl2.ctel_no = tbl1.Tel_No) vTab
                         where vtab.rn = 1;
                        • 9. Re: Distinct Function
                          976439
                          thanks guys. It is the first date_time that I would be looking for. I will try out the examples and see how i get on.

                          Thanks for all your help.
                          • 10. Re: Distinct Function
                            Etbin
                            It is the first date_time that I would be looking for ...
                            You'd be better off if you rephrase that as Oracle heap tables are sets of rows i.e. no particular order is implicit or implied.
                            Use greatest/least or maximal/minimal date_time (value) instead.

                            Regards

                            Etbin
                            • 11. Re: Distinct Function
                              Nicosa-Oracle
                              Hi,

                              Please clarify first : Do you want to list duplicates ? or remove them ?
                              And in case of removal : from which table do you want to delete ?
                              973436 wrote:
                              The following is an example of my resultset:
                              Order_No | Tel_No | user_id | name |       date_time        | product | company_name
                              -------------    ---------    ---------    -------   ---------------------------    ----------    ---------------------
                              1234          5678      12         ABC     10-DEC-12 17:01:21   DF          HUMPTY
                              1234          5678      12         ABC     10-DEC-12 16:22:11   DF          HUMPTY
                              1234          5678      12         ABC     10-DEC-12 16:52:28   DF          HUMPTY
                              1234          5678      12         ABC     10-DEC-12 17:46:15   DF          HUMPTY
                              So what I would like is to check the first four columns (order_no, tel_no, user_id and name) and if this have any duplicates than remove these even though the date_time values are not distinct. So this should leave me with the following only.
                              Order_No | Tel_No | user_id | name |       date_time        | product | company_name
                              -------------    ---------    ---------    -------   ---------------------------    ----------    ---------------------
                              1234          5678      12         ABC     10-DEC-12 17:01:21   DF          HUMPTY
                              I fail to understand to logic that leads you to keep that row. It's obviously not the first, neither the last date_time.
                              Once you can phrase the logic, removing all the others would be easy.

                              Let's say you want to remove all duplicate but keep the last date_time, I would try something like that :
                              delete from your_table a
                              where exists (
                                 select null
                                 from your_table b
                                 where b.order_no=a.order_no
                                 and b.tel_no=a.tel_no
                                 and b.user_id=a.user_id
                                 and b.name=a.name
                                 and b.date_time>a.date_time
                              ) ;