i want to create a windowing function row_number partitioned by id column and order by code where i should get E first and rest records next

user_8857130 0
Viewed 62
id code
1   D
1   S
1   E
1   Y

I want to create a windowing function row_number partitioned by id column and order by code where i should get E first and rest records next.

Expected output is like

id   code
1     E
1     D
1     S
1     Y

i want E to be coming first in ordering, Alphabetical won't work. is there any othere way? custom sorting or any other hive inbuilt function

hive sql
2 Answers
select * from ids order by(case when code = 'E' then 1 else 2 end), code  ;
user_88571300

Use simple CASE expression in ORDER BY

with t             --sample data only not part of main query
AS                  
(
select 1 id  , 'D'  code UNION ALL
select 1 id  , 'D'  code UNION ALL
select 1 id  , 'S'  code UNION ALL
select 1 id  , 'E'  code UNION ALL
select 1 id  , 'Y'  code
)                    --sample data end
SELECT * FROM t 
             ORDER BY 
                case 
                    code WHEN 'E' 
                           then 1 
                           else 2 
                end,code;

Output (Tested on Hive 0.13 )

MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 5.08 sec   HDFS Read: 289 HDFS Write: 20 SUCCESS
Total MapReduce CPU Time Spent: 5 seconds 80 msec
OK
1   E
1   D
1   D
1   S
1   Y
Time taken: 44.95 seconds, Fetched: 5 row(s)
user_79985910
Related