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.
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.
Originally posted by neelamchalam
Hi, this query will fail if you add one more record ('BB',150) to your table.
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.
Originally posted by grofaty
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