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.

Select without order by returns dataset unordered by definition. Read this: https://stackoverflow.com/a/54542826/2700344 and this also this: https://stackoverflow.com/a/47416027/2700344 — leftjoin, Apr 27 '19 at 07:47

score 0 · Answer 1 · answered Apr 27 '19 at 07:08

0

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;

answered Apr 27 '19 at 07:08

thanh ngo

834
5
9

WIth the above equation I am able to get the results. But once I insert it back to my destination table without rank..again the records are getting reordered. I am updating the question with the trail case of your answer. – USB Apr 27 '19 at 07:36

score 0 · Answer 2 · answered Apr 27 '19 at 07:52

0

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;

answered Apr 27 '19 at 07:52

forpas

160,666
10
38
76

yes it works during select but once I insert it to my destination table..the order goes. insert overwrite table TestSort select * from test order by case when name = 'LEADER' then '0' else '1' end, groupid, name, subjectcode; – USB Apr 27 '19 at 08:25
1

There is no order inside a table. You have to fetch the rows of the table again by using ORDER BY. – forpas Apr 27 '19 at 08:37

Issues in Ordering up table values in hive

2 Answers2