Oracle SQL Case

Question

My query:

select school, rating, name
from mytable
where school = 'SchoolN';

Results in:

+--------+------+-----+
|School  |Rating|Name |
+--------+------+-----+
|SchoolN |  A   |Name1|
|SchoolN |  B   |Name2|
|SchoolN |  A   |Name3|
|SchoolN |  A   |Name4|
|SchoolN |  A   |Name5|
|SchoolN |  B   |Name6|
|SchoolN |  B   |Name7|
|SchoolN |  B   |Name8|
|SchoolN |  B   |Name9|
+--------+------+-----+

What I wanted is to make name like this:

+--------+------+---------+
|School  |Rating|Name     |
+--------+------+---------+
|SchoolN |  A   |i-Name1  |
|SchoolN |  B   |i-Name2  |
|SchoolN |  A   |ii-Name3 |
|SchoolN |  A   |iii-Name4|
|SchoolN |  A   |iv-Name5 |
|SchoolN |  B   |ii-Name6 |
|SchoolN |  B   |iii-Name7|
|SchoolN |  B   |iv-Name8 |
|SchoolN |  B   |v-Name9  |
+--------+------+---------+

I tried:

select school, rating,
case when rownum = 1 then 'i-' || name
     when rownum = 2 then 'ii-' || name
     when rownum = 3 then 'iii-' || name
     when rownum = 4 then 'iv-' || name
     when rownum = 5 then 'v-' || name
end Name
from mytable
where school = 'SchoolN';

But the result is wrong:

+--------+------+---------+
|School  |Rating|Name     |
+--------+------+---------+
|SchoolN |  A   |i-Name1  |
|SchoolN |  B   |ii-Name2 |
|SchoolN |  A   |iii-Name3|
|SchoolN |  A   |iv-Name4 |
|SchoolN |  A   |v-Name5  |
|SchoolN |  B   |Name6    |
|SchoolN |  B   |Name7    | 
|SchoolN |  B   |Name8    |
|SchoolN |  B   |Name9    |
+--------+------+---------+

Answer 1

You may use dense_rank() with fmRM format model option of character conversion function(to_char) :

select t.school,
       t.rating,
       to_char(dense_rank() over(partition by rating order by name), 'fmrm') 
       || '-' ||t.name name
  from mytable t
 order by t.name;

SQL Fiddle Demo

Answer 2

ROWNUM is just the number of the row within a result set. What you actually want is the number of the name within each rating. So that means you need an analytic function like row_number():

with cte as (
    select school, rating, name, 
           row_number() over (partition by  school, rating order by name) as rn       
    from mytable
)
select cte.school, 
       cte.rating,
       case when cte.rn = 1 then 'i-' || name
            when cte.rn = 2 then 'ii-' || name
            when cte.rn = 3 then 'iii-' || name
            when cte.rn = 4 then 'iv-' || name
            when cte.rn = 5 then 'v-' || name
        end Name
from cte
where cte.school = 'SchoolN'
;

This is the minimal change to your code required to produce the result set you want. I suggest you also incorporate @KaushikNayak suggestion and deploy the rn format mask, because that will make your code more compact and less brittle:

...
select cte.school, 
       cte.rating,
       to_char(cte.rn, 'rn')|| '-' || name as Name
from cte
...

Pass the format mask in lower case ('rn' ) to get the output in lower case : i, ii, etc.

Answer 3

You could make use of Roman numeral format specifier RN in TO_CHAR

SELECT
    level as n,
    TO_CHAR(level,'RN') as roman
FROM
    dual
CONNECT BY
    level <= 10;


         N ROMAN          
---------- ---------------
         1               I
         2              II
         3             III
         4              IV
         5               V
         6              VI
         7             VII
         8            VIII
         9              IX
        10               X

10 rows selected. 

So, In the APC's query, you could do to_char(rn,'RN') || name as name