SQL Problem

[David]

Table A
Name Score
A 12
A 22
A 88
B 34
B 12
C 33
C 55
Table B

Name Score
A 12
NuLL 22
Null 88
B 34
Null 12
C 33
NUll 55

How can i create table b base on table a ?

[AK]

CASE WHEN ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY SCORE) = 1 THEN
NAME ELSE NULL END

[Odd Morten Sve?s]

ak************@yahoo.com (AK) wrote in message news:<46**************************@posting.google. com>...

CASE WHEN ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY SCORE) = 1 THEN
NAME ELSE NULL END

Hi

Is can this be runn on Oracle? I would be pleased to know more about
this type of syntax.

In a traditionel but of cource cumbersome syntax the following will do
the trick. (Not tested, so it migth be some syntax errors)

insert into b
(name,score)
select decode(rowid ,minrowid, name , null)
, score
(select rowid
, name
, score
, (select min(rowid) from a a2 where a2.name = a1.name ) minrowid
from a a1)

But the simples would be to write an pl/sql prgram to solve this,
includinga cursor, a loop and a check if the name has changed.
Regards
Odd M

[AK]

>

Is can this be runn on Oracle? I would be pleased to know more about
this type of syntax.

Yes, it runs on Oracle 9i. If you google 'OLAP functions', 'analytical
functions' you'll get a lot of useful links