469,645 Members | 1,239 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

SQL Problem

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 ?
Jul 19 '05 #1
3 2342
AK
CASE WHEN ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY SCORE) = 1 THEN
NAME ELSE NULL END
Jul 19 '05 #2
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
Jul 19 '05 #3
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
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Kostatus | last post: by
117 posts views Thread by Peter Olcott | last post: by
18 posts views Thread by Ian Stanley | last post: by
28 posts views Thread by Jon Davis | last post: by
6 posts views Thread by Ammar | last post: by
2 posts views Thread by Mike Collins | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.