Hi Sir.....
How to sequence alphabet using sql query that will output like this :
column a, column_category ,column_points
1-A sample-2 A
1-B sample-2 B
2-A sample-3 A
2-B sample-3 B
2-C sample-3 C
Its a little hard to work out exactly what you are asking...but I'll have a stab at it
SQL> drop table T purge;
Table dropped.
SQL>
SQL> create table T (
2 cola varchar2(10),
3 col_cat varchar2(20),
4 col_points varchar2(1)
5 );
Table created.
SQL>
SQL> insert into T values ('1-A', 'sample-2', 'A');
1 row created.
SQL> insert into T values ('1-B', 'sample-2', 'B');
1 row created.
SQL> insert into T values ('2-A', 'sample-3', 'A');
1 row created.
SQL> insert into T values ('2-B', 'sample-3', 'B');
1 row created.
SQL> insert into T values ('2-C', 'sample-3', 'C' );
1 row created.
SQL>
SQL> select * from T;
COLA COL_CAT C
---------- -------------------- -
1-A sample-2 A
1-B sample-2 B
2-A sample-3 A
2-B sample-3 B
2-C sample-3 C
I'm assuming you want to break up "cola" to sort it by the leading number then the letter ? If so, then
SQL> select *
2 from T
3 order by
4 to_number(substr(cola,1,instr(cola,'-')-1)),
5 substr(cola,instr(cola,'-')+1);
COLA COL_CAT C
---------- -------------------- -
1-A sample-2 A
1-B sample-2 B
2-A sample-3 A
2-B sample-3 B
2-C sample-3 C
and you see what those expressions are here
SQL>
SQL> select to_number(substr(cola,1,instr(cola,'-')-1)) s1,
2 substr(cola,instr(cola,'-')+1) s2,
3 t.*
4 from T
5 order by
6 to_number(substr(cola,1,instr(cola,'-')-1)),
7 substr(cola,instr(cola,'-')+1);
S1 S2 COLA COL_CAT C
---------- ---------- ---------- -------------------- -
1 A 1-A sample-2 A
1 B 1-B sample-2 B
2 A 2-A sample-3 A
2 B 2-B sample-3 B
2 C 2-C sample-3 C
SQL>