Oracle Sort by column value

Question

I am trying to interpret my real time scenario as follows. I have 2 tables and I need a query to return sorted students based on Course value. In the following example, the resulted Student Ids will be in order of 2, 3, 1.

STUDENT
ID   NAME PRIORITY STATUS
1    ABC
2    BCD
3    CDE

VARIABLE
V_ID   STU_ID   KEY          VALUE
1      1        name         name1
2      1        **course**   MCA
3      1        place        place1
4      2        name         name2
5      2        **course**   BCA
6      2        place        place2
7      3        name         name1
8      3        **course**   FCA
9      3        place        place1

Desired result(after sort, show data from both tables in the same sort order.):

ID   NAME       KEY          VALUE
2    BCD        name         name2
2    BCD        **course**   BCA
2    BCD        place        place2
3    CDE        name         name1
3    CDE        **course**   FCA
3    CDE        place        place1
1    ABC        name         name1
1    ABC        **course**   MCA
1    ABC        place        place1

Your help will be appreciated.

thanks, Swamy.

Answer 1

Just need to join to Variable twice once for course to get the order and once for all the data. The join for the order needs to be just on the student and key so all records get the "sort order course" applied.

Though I'm not sure what you're sorting on after the course value for each student to get your results. the key makes some sense but without hardcoding a case statement I can't get to your results.

With student (ID,   NAME, PRIORITY, STATUS) as  (
SELECT 1,    'ABC', NULL, NULL FROM DUAL UNION ALL
SELECT 2,    'BCD', NULL, NULL FROM DUAL UNION ALL
SELECT 3,    'CDE', NULL, NULL FROM DUAL),
"VARIABLE" (V_ID,   STU_ID,   "KEY",          "VALUE") as (
SELECT 1,      1,        'name',         'name1' FROM DUAL UNION ALL
SELECT 2,      1,        '**course**',   'MCA' FROM DUAL UNION ALL
SELECT 3,      1,        'place',        'place1' FROM DUAL UNION ALL
SELECT 4,      2,        'name',         'name2' FROM DUAL UNION ALL
SELECT 5,      2,        '**course**',   'BCA' FROM DUAL UNION ALL
SELECT 6,      2,        'place',        'place2' FROM DUAL UNION ALL
SELECT 7,      3,        'name',         'name1' FROM DUAL UNION ALL
SELECT 8,      3,        '**course**',   'FCA' FROM DUAL UNION ALL
SELECT 9 ,     3,        'place',        'lace1' FROM DUAL)

SELECT V.STU_ID, S.Name, V.Key, V.VALUE
FROM STUDENT S
INNER JOIN VARIABLE V
 on S.ID = V.Stu_ID
LEFT JOIN VARIABLE V2
 on S.ID = V2.Stu_ID and V2.Key = '**course**'
ORDER BY V2.Value, V.Key

Answer 2

It seems that you just need to use a regular join, like this

SELECT student.id
from variable
join student 
on variable.stu_id= student.id
where variable.key= '**course**'
order by variable.value asc