0

I have created a Hive table

CREATE external TABLE test.partordtst (name string,age string,priority string) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '~' 
LOCATION '/DataprovidersDEV/Tom/Hive/Test/';

The values in the file that is used for creating the table is as follows

jijo~10~10
jijo~10~2
jijo~10~3
jijo~09~2
jijo~08~3
jijo~07~4
jijo~06~5
jijo~05~6

I wrote a query to select the lowest priority record in a partition

select * from 
(
select partordtst.*,row_number() over(partition by name,age order by priority asc) rn
from partordtst
)abc
where rn=1
order by name,age;

I got the output as below

jijo    05      6       1
jijo    06      5       1
jijo    07      4       1
jijo    08      3       1
jijo    09      2       1
jijo    10      10      1

In the output, I expect the following value

jijo    10      2      1

instead of

jijo    10      10      1

Why is Hive not doing the sorting properly. To be precise, why is asc not working?

mazaneicha
  • 8,794
  • 4
  • 33
  • 52
TomG
  • 281
  • 1
  • 2
  • 20
  • 1
    It properly sorts strings in lexicographical order. To achieve numeric ordering you need to `cast(priority as int)`, most likely. – mazaneicha Apr 16 '21 at 00:27
  • https://stackoverflow.com/questions/45950646/what-is-lexicographical-order/60604165#60604165 – TomG Apr 16 '21 at 04:18

0 Answers0