This discussion is archived
11 Replies Latest reply: Jan 2, 2013 12:40 AM by Nicosa RSS

Distinct Function

976439 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    ) ;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points