Results 1 to 7 of 7

Thread: Distinct

  1. #1
    Join Date
    Apr 2004
    Posts
    113

    Unanswered: Distinct

    I am trying to insert and perform a select distinct at the same time
    I need all of the columns in the current SELECT DISTINCT, but I want it to
    be DISTINCT on the "LIST_ACCT_NO" column. Right now
    there are duplicates in that column.


    INSERT INTO FINAL
    SELECT DISTINCT LIST_NAME,LIST_ACCT_NO,MARKET_CODE,EMAIL,LIST_PHON E_NO,LIST_PHONE_NO,TV_MTH,TV_HIST,EVENT_MTH,EVENT_ HIST
    IMP_MTH,IMP_HIST,AD_FLAG,UNSUB,UNSUB_FROM_FLAG FROM TJUGG_FINAL;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    So suppose you have the following 2 rows in the table:
    Code:
    LIST_NAME  LIST_ACCT_NO  MARKET_CODE  EMAIL  ...
    XXX        123           AAA          abc@def
    YYY        123           CCC          ghi@jkl
    (Note: same LIST_ACCT_NO on both rows). What values do you want to insert into FINAL?

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    keep in mind that ALL CULUMNS listed in the select statement
    consist of what would be DISTINCT. Not just the first column name
    after the distinct clause. So, if you include all columns in your
    select distinct clause then erssentially you are selecting most of
    the table most probably.

    So, you need to figure out WHICH LIST_ACCT_NO rows you
    actually want from the TJUGG_FINAL table since you have
    duplicates of LIST_ACCT_NO. Which row is the row you want?
    How is the row determined?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Right now there are duplicates in that column. (LIST_ACCT_NO)
    So who/what/how to decide which row gets inserted?
    If all the columns are identical where LIST_ACCT_NO = LIST_ACCT_NO,
    then eliminate the duplicate rows before doing the insert.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    A good rule of thumb:
    validate your data before manipulating it.
    This will save you valuable time down the road.


    in your situation it might be productive to test out your
    subqueries as select statements BEFORE you try to do
    updates or inserts
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Apr 2004
    Posts
    113
    I am trying to get rid of all duplicate rows where the list_acct_no=list_acct_no NO MATTER what the other rows have in it.

    How do I do that?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://asktom.oracle.com
    Do a keyword search on REMOVE DUPLICATE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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