How add a column with a value depending on a type in an oracle query

I need to display a column X depending on the result of a query:}

I need like that

select 1 id_t, 'Book 1' val, 'C1' categ, 'S' res from dual union
select 2 id_t, 'Book 2' val, 'C1' categ, null res from dual union
select 3 id_t, 'Book 3' val, 'C1' categ, null res from dual union

select 4 id_t, 'Book 4' val, 'C2' categ, 'S' res from dual union

select 5 id_t, 'Book 5' val, 'C3' categ, 'S' res from dual union
select 6 id_t, 'Book 6' val, 'C3' categ, null res from dual

I need to group the value of the "res" column depending on the category

I haven’t tried anything yet

>Solution :

Sample table:

SQL> select * From test;

      ID_T VAL    CATEG RES
---------- ------ ----- ---
         1 Book 1 C1
         2 Book 2 C1
         3 Book 3 C1
         4 Book 4 C2
         5 Book 5 C3
         6 Book 6 C3

6 rows selected.

SQL>

One option is to use row_number analytic function to sort rows per category by ID, and then set res for rows that ranked as the highest:

SQL> with temp as
  2    (select id_t, val, categ,
  3       row_number() over (partition by categ order by id_t) rn
  4    from test
  5    )
  6  select id_t, val, categ,
  7    case when rn = 1 then 'S' end res
  8  from temp;

      ID_T VAL    CATEG RES
---------- ------ ----- ---
         1 Book 1 C1    S
         2 Book 2 C1
         3 Book 3 C1
         4 Book 4 C2    S
         5 Book 5 C3    S
         6 Book 6 C3

6 rows selected.

SQL>