Oracle PL-SQL problem 1

[borntorun]

HI,

I have 15 columns... in my result set.

I have two columns which are:

COLA COLB
JOHN 23
JOhn 0.1
Jack 15
James 1000
James 34
James 0.1

So i need to create a new column and searches through col a, and where the value occurs more than once in the new column put a 1 next to the row with the highest value and 2 for all others. If it occurs just once put just a one.

I am getting a little lost about how to do this. All other columns need to be returned in the query but mustst be effected.

Thanks.

 

[PHV]

A two pass update query ?
UPDATE yourTable SET newCol = 2

UPDATE yourTable SET newCol = 1
WHERE COLB = (SELECT Max(COLB) FROM yourTable A WHERE A.COLA = yourTable.COLA)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dnoeth]

Hi borntorun,
use an analytic function:

select
....,
cola,
colb,
case when colb = max(colb) over (partition by cola)
then 1
else 2
end
from tab
....


Dieter