Hi,

I would like to write SQL witch will replace values in 'duplicate columns' with 'null'.
Thanks,
Grofaty

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

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

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

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

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

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

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

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

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
rudy
http://r937.com/

Hi,

Thanks r937 and ARWinner.

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

Grofaty