Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to write SQL with reduce 'duplicate column values'

    Hi,

    I would like to write SQL witch will replace values in 'duplicate columns' with 'null'.
    Code:
    Souce data:
    COL1     COL2
    ----------------
    AAA        100
    AAA        200
    AAA        300
    BBB          50
    BBB          40
    CCC         10
    
    
    SQL returned data:
    COL1    COL2
    ---------------
    AAA        100
    -            200
    -            300
    BBB          50
    -              40
    CCC         10
    Thanks,
    Grofaty
    Last edited by grofaty; 10-07-03 at 05:34.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    try:

    update mytable as a set a.col1 = NULL where (a.col1,a.col1) not in
    (select b.col1,min(b.col2) from mytable as b group by col1)

    HTH

    Andy

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Sorry for my incorrectness. The data must stay in origional state. I would just like to write Select statement not update. I would like to write select with omit of the repeated data to get more readable data.

    Thanks,
    Grofaty

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You want something like:

    SELECT case when (a.col1,a.col2) not in
    (select b.col1,min(b.col2) from mytable as b group by col1)
    then NULL else a.col1 end as col1,a.col2 from mytable as a

    HTH

    Andy

  5. #5
    Join Date
    Jul 2003
    Location
    india
    Posts
    15
    Originally posted by ARWinner
    You want something like:

    SELECT case when (a.col1,a.col2) not in
    (select b.col1,min(b.col2) from mytable as b group by col1)
    then NULL else a.col1 end as col1,a.col2 from mytable as a

    HTH

    Andy
    Dear Andy ,

    could you please guide me how to get this type of knowledge. suggest me some books and ways so i can able to write such a query.

    thanking you,
    Chalam N

  6. #6
    Join Date
    Jul 2003
    Location
    india
    Posts
    15
    Originally posted by ARWinner
    You want something like:

    SELECT case when (a.col1,a.col2) not in
    (select b.col1,min(b.col2) from mytable as b group by col1)
    then NULL else a.col1 end as col1,a.col2 from mytable as a

    HTH

    Andy
    Hi, this query will fail if you add one more record ('BB',150) to your table.

    source data

    COL1 COL2

    ---- -----------

    AAA 100

    AAA 200

    AAA 300

    BB 50

    BB 40

    CCC 10

    BB 150

    the result coming like the following

    COL1 COL2

    ---- -----------

    AAA 100

    - 200

    - 300

    - 50

    BB 40

    CCC 10

    - 150

    where 150 should be for BB not for CC

    Thanking you,
    Chalam N

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Chalam,
    Most of what I know is from experience, looking at samples, a few classes, etc. There is one book that is pretty good and it is free. You can download it from:

    http://ourworld.compuserve.com/homep...l/HTM_COOK.HTM

    Other places you can look:

    Search the archives in this forum
    Check out searchDatabase.com
    Read the IBM manuals and throughly understand the grammars of each statement.

    Andy


    Originally posted by neelamchalam
    Dear Andy ,

    could you please guide me how to get this type of knowledge. suggest me some books and ways so i can able to write such a query.

    thanking you,
    Chalam N

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Chalam,
    You are wrong. The last line in the result set is showing NULL for BB just as you would expect. Your problem is one of perception. You expect the result set to be sorted on the original values of col1. There is no ORDER BY clause, so the order is basically random. THe BB,150 row was added last so that is usually where it will appear in an unsorted result set.

    Andy

    Originally posted by neelamchalam
    Hi, this query will fail if you add one more record ('BB',150) to your table.

    source data

    COL1 COL2

    ---- -----------

    AAA 100

    AAA 200

    AAA 300

    BB 50

    BB 40

    CCC 10

    BB 150

    the result coming like the following

    COL1 COL2

    ---- -----------

    AAA 100

    - 200

    - 300

    - 50

    BB 40

    CCC 10

    - 150

    where 150 should be for BB not for CC

    Thanking you,
    Chalam N

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Hi ARWinner,

    Thanks for sql. But I think this SQL does't do the job.

    SQL you wrote:
    SELECT CASE WHEN (A.COL1,A.COL2) not in (select b.col1, min(b.col2) from mytable as b group by col1) then null else a.col1 end as col1, a.col2 from mytable as a

    Result of sql
    COL1 COL2
    ---- -----------
    AAA 100
    - 200
    - 300
    - 50
    BBB 40
    CCC 10


    Desired result
    COL1 COL2
    ---------------
    AAA 100
    - 200
    - 300
    BBB 50
    - 40
    CCC 10

    You notice the BBB at desired result starts at 50, your sql starts at 40. Is this a sort problem? Order of COL2 is not important for me. Just the COL1 should be sorted.

    Thanks,
    Grofaty
    Last edited by grofaty; 10-16-03 at 02:53.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Grofaty,
    I think I see what you are after. I thought you just wanted only 1 row to list the actual value of COL1, not taking in to account anything else. So I arbitrarily chose to list the value only with the minimum value of COL2. What (I think) you actually want, is that you have a query that is generating a result set sorted on COL1. You want only the rows where the first time a value appears in COL1 of THAT result set to show the value otherwise you want a NULL.

    If I am correct in this, could you post the query and what version of DB2 you are using, and I should be able to come up with a solution.

    Andy


    Originally posted by grofaty
    Hi ARWinner,

    Thanks for sql. But I think this SQL does't do the job.

    SQL you wrote:
    SELECT CASE WHEN (A.COL1,A.COL2) not in (select b.col1, min(b.col2) from mytable as b group by col1) then null else a.col1 end as col1, a.col2 from mytable as a

    Result of sql
    COL1 COL2
    ---- -----------
    AAA 100
    - 200
    - 300
    - 50
    BBB 40
    CCC 10


    Desired result
    COL1 COL2
    ---------------
    AAA 100
    - 200
    - 300
    BBB 50
    - 40
    CCC 10

    You notice the BBB at desired result starts at 50, your sql starts at 40. Is this a sort problem? Order of COL2 is not important for me. Just the COL1 should be sorted.

    Thanks,
    Grofaty

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    andy, your code is quite nice, but it utilizes a feature of sql that few databases support, the where row in derived table thingie

    i didn't even know that that row thingie is supported in db2 (i don't have db2 to test it, but it's nice to know)

    in any case, here's an equivalent using just a plain ordinary correlated subselect

    note that you still need to sort on col1, but then you would display the column called print1 instead
    Code:
    select col1
         , case when col2 > 
                     ( select min(col2) 
                         from thetable 
                        where col1 = a.col1 ) 
                then '   ' 
                else a.col1 end as print1
         , col2 
      from thetable a
    order by col1, col2
    rudy
    http://r937.com/

  12. #12
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thanks r937 and ARWinner.

    The last post from r937 does the job. Thanks a lot!

    Grofaty

Posting Permissions

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