Using order by clause in Apache Hive.

Order by clause in Apache Hive is used to arrange data of column(s) in desired order. We can arrange the column(s) data either in ascending order or descending order. Ascending order is the default order.

You can practise below queries to understand order-by clause practically.

1) Create a table called employee using this article.

2) Run select query and check the output.

Query:

select * from employee;

3) Sort the output data by deptno column and compare second step output to understand how data is sorted.

The following query sorts deptno column data in ascending order.

Query :

select * from employee order by deptno;

By default output will be in ascending order.

4) Use desc keyword to sort in descending order.

The following query sorts deptno column data in descending order.

select * from employee order by deptno desc.

5) We can use multiple columns in order by clause.

The following query sorts first deptno column data and salary column data later.

Query:

select * from employee order by deptno,salary;

Compare the above output with 3 step output to understand how multi column order by clause works.

After sorting first column, Order by picks every uniq value in first column i.e. deptno and sorts second column data i.e. salary data against deptno uniq value. In the above step , Only for deptno value 15 ,  it sorts salary values 325000,350000 in ascending order  as rest of the data for deptno was already in ascending order.