Issues in Ordering up table values in hive

Question

I would like to order the values of my table in specific manner.

I have a source table(TestTable) with below data

groupId,Name,subjectCode,PA
1,LEADER,T,67
1,Prem,P,67
1,Prem,T,89
1,Anjali,T,124
1,LEADER,P,234
1,Anjali,P,234
1,Anjali,NP,45
1,Prem,NP,23
1,LEADER,NP,123

and my expected results for new table TestSort is

1,LEADER,NP,123
1,LEADER,P,234
1,LEADER,T,67
1,Anjali,NP,45
1,Anjali,P,234
1,Anjali,T,124
1,Prem,NP,23
1,Prem,P,67
1,Prem,T,89

I need to order 1st three columns in asc and also I have to place the leader record at the top it self.

I tried to do the same ,But I am not getting the results as expected

insert overwrite table TestSort select * from TestTable order by groupId asc,Name asc,subjectCode asc;

The result what I get is

groupId,Name,subjectCode,PA
1,LEADER,T,67
1,LEADER,NP,123
1,Anjali,NP,45
1,Anjali,P,234
1,LEADER,P,234
1,Anjali,T,124
1,Prem,T,89
1,Prem,NP,23
1,Prem,P,67

Am I doing anything wrong somewhere.

TRAIL 1

  insert overwrite table TestSort select * from TestTable 
order by 
  case when name = 'LEADER' then '0' else '1' end, 
  groupid, 
  name,
  subjectcode;;
    select * from TestSort;

The result for above query is

    groupId,Name,subjectCode,PA
1,Anjali,NP,45
1,LEADER,T,67
1,Prem,T,89
1,Prem,P,67
1,LEADER,NP,123
1,Prem,NP,23
1,Anjali,T,124
1,Anjali,P,234
1,LEADER,P,234

My expectation is to insert values into a new table TestSort. Select query gives me the expected result, but once I insert the values into TestSort,the order is getting changed.

Answer 1

I think this query will work

select *, case when Name = 'LEADER' then '1' else '0' end as rank from Test order by rank desc, groupId, Name ,subjectCode asc;

Answer 2

You can put the condition directly in the ORDER BY clause:

select * from test 
order by 
  case when name = 'LEADER' then '0' else '1' end, 
  groupid, 
  name,
  subjectcode;