CODEWITHSUNDEEP
sqloracle-database
justlearn2
justlearn2

Reputation: 31

how to add new column(not exist) in select result?

I have this table :

name   dep_id   age
admin  1         22  
admin2 1         23
admi   2         24
ad     2         22
aa     2         23

i want to return a result that contain new column called " dep_num " and this column not exist in the main table , and its count the same dep_id , i mean admin and admin2 have the same dep_id so its contain 1,2 and then admi have differnet dep_id so its count from the first 1 and so .. the resut like :

dep_num       name   dep_id   age
1             admin  1         22  
2             admin2 1         23
1             admi   2         24
2             ad     2         22
3             aa     2         23

Upvotes: 0

Views: 44

Answers (2)

kanagaraj
kanagaraj

Reputation: 442

Try below query:

select RANK() OVER (PARTITION BY dep_id ORDER BY id) dept_num,
  name,
  dep_id,
  age
from table

Upvotes: 1

Joop Eggen
Joop Eggen

Reputation: 109567

Let us assume the table's name is table_deps:

alter table table_deps
add (dep_num int default 0);

update table_deps d
set dep_num = 1 + (select count(*)
                   from table_deps p 
                   where p.dep_id = d.dep_id
                   and p.name < d.name);

Not sure whether the UPDATE works.

Upvotes: 0

Related Questions