EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Hive Order By

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Hive Tutorial » Hive Order By

hive order by

Introduction to Hive Order By

Hive is a data warehousing facility provided by Apache. Hive is built on top of the Hadoop Distributed File System (HDFS) to write, read, querying, and manage large structured or semi-structured data in distributed storage systems such as HDFS. Each HiveQL will be converted to a MapReduce job in the backend. Hive provides Hive Query Language (HiveQL) that is like normal SQL in RDBMS. Like SQL, HiveQL also provides the ORDER BY clause which can be used with the SELECT clause. ORDER BY function is used to sort data either in ascending or descending order on columns specified by the user.

Syntax in Hive Order By

Below is the syntax in hive order by:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Basic Syntax:

SELECT <column name1>,<column name2> FROM <table name> ORDER BY <column name>;

ORDER BY clause along with all other Clauses:

SELECT DISTINCT <column name1>, <column name2>, <column name3> FROM <table name> WHERE <where condition> GROUP BY <column names…> HAVING <having conditions> ORDER BY <column names> <ASC / DESC> LIMIT <limiting number>;

You may also specify ORDER BY 1 or ORDER BY 2 where 1 and 2 represent the column number instead of using ORDER BY <column names>.  ORDER BY <column number> can only be used when column names are specified in the SELECT clause and not with the SELECT * clause.

How can we Use ORDER BY Function in Hive?

Below explanation shows that how can we use order by function in a hive:

1. ORDER BY Ascending and Descending

  • By default, the sorting order will be ascending, which shows the smallest value in the beginning and the largest value at the end of the result, even if you do not specify it explicitly.
  • You can also specify ORDER BY <column names> ASC for ascending order and ORDER BY <column name> DESC for sorting the result in descending order or the specified column.
  • The HiveQL syntax for ODER BY ascending order can be shown as below:

SELECT <column name1>,<column name2> FROM <table name> ORDER BY <column name> ASC;

  • The HiveQL syntax for ODER BY descending order can be represented as below:

SELECT <column name1>,<column name2> FROM <table name> ORDER BY <column name> DESC;

  • Ascending and descending clauses can be simultaneously used with multiple columns in the ORDER BY clause, as shown below:

SELECT <column name1>,<column name2> FROM <table name> ORDER BY <column name1> ASC <column name2> DESC;

2. ORDER BY and NULL Values

  • The latest versions of Hive supports sorting order for NULL value also.
  • By default, the sorting order of NULL values for ORDER BY <column name> ASC is NULLS FIRST. It sorts all the NULL values to the beginning of the sorted result.
  • Similarly, the sorting order of NULL values for ORDER BY <column names> DESC is NULLS LAST by default. It sorts all the NULL values to the end of the sorted result.
  • You can also specify NULLS FIRST, and NULLS LAST and ORDER BY <column name> ASC or with ORDER BY <column name> DESC according to your requirements convenience.
  • Syntax of NULLS FIRST clause along with ORDER BY ascending:

SELECT <column name1>,<column name2> FROM <table name> ORDER BY <column name> ASC NULLS LAST;

  • Syntax of NULLS LAST clause along with ORDER BY descending:

SELECT <column name1>,<column name2> FROM <table name> ORDER BY <column name> DESC NULLS FIRST;

3. ORDER BY and LIMIT Clause

  • LIMIT clause is optional with the ORDER BY clause.
  • LIMIT clause can be used to improve the performance. LIMIT clause can be used to avoid unnecessary data processing.
  • LIMIT clause will select only the limited number of values from the result for the verification purpose.
  • LIMIT clause with ORDER By clause can be shown as below:  

SELECT <column name1>,<column name2> FROM <table name> ORDER BY <column name> ASC LIMIT 10;

  • ORDER BY clause can also be combined with the OFFSET clause and LIMIT clause to reduce the result’s quantity. Syntax of the same can be shown as:

SELECT <column name1>,<column name2> FROM <table name> ORDER BY <column name> ASC LIMIT 10 OFFSET 10;

Examples in Hive Order By

Consider the following sample table Employee which contains Employee Id as EmpID, Employee name as EmpName, Designation, Department as Dept,  Job Level as JL, and Salary.

order by1

Example #1

Code:

SELECT * FROM Employee ORDER BY JL ASC;

Output:

Example 1

Example #2

Code:

SELECT * FROM Employee ORDER BY Salary DESC LIMIT 3;

Output:

Example 2

Example #3

Code:

SELECT EmpId, EmpName, Designation, Dept FROM Employee where Salary < 50000 ORDER BY EmpName ASC JL ASC;

Output:

order by4

Conclusion

ORDER BY in Hive allows you to sort data in either ascending or descending order. ORDER BY can be combined with other clauses to get the sorted data. ORDER BY is different from the SORT BY as SORT BY sorts the data within a reducer but ORDER BY sorts the entire data.

Popular Course in this category
Sale
Hive Training (2 Courses, 5+ Projects)2 Online Courses | 5 Hands-on Projects | 25+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (9,168 ratings)
Course Price

View Course

Related Courses
Hadoop Training Program (20 Courses, 14+ Projects, 4 Quizzes)All in One Data Science Bundle (360+ Courses, 50+ projects)

Recommended Articles

This is a guide to Hive Order By. Here we discuss How we can use order by function in a hive, and different examples with codes and outputs. You can also go through our other related articles to learn more –

  1. Hive Built-in Functions
  2. What is a Hive?
  3. How To Install Hive
  4. Hive Function
  5. Tableau Versions
  6. Hive Installation
  7. Examples of Python Built-in Functions
  8. Comprehensive Guide to Partitioning in Hive
  9. How to Work ORDER BY in Oracle?

Hive Training (2 Courses, 5+ Projects)

2 Online Courses

5 Hands-on Projects

25+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Hive Tutorial
  • Basics
    • Hive JDBC Driver
    • What is a Hive
    • Hive Architecture
    • Hive Installation
    • How To Install Hive
    • Hive Versions
    • Hive Commands
    • Hive Data Types
    • Hive Built-in Functions
    • Hive Function
    • Hive String Functions
    • Date Functions in Hive
    • Hive Table
    • Hive Drop Table
    • Hive Show Tables
    • Hive Group By
    • Hive Order By
    • Hive Cluster By
    • Joins in Hive
    • Hive Inner Join
    • Map Join in Hive
    • Hive nvl
    • Hive UDF
    • Dynamic Partitioning in Hive
    • HiveQL
    • HiveQL Queries
    • HiveQL Group By
    • Partitioning in Hive
    • Bucketing in Hive
    • Views in Hive
    • Indexes in Hive
    • External Table in Hive
    • Hive TimeStamp
    • Hive Database
    • Hive Interview Questions
    • Hive insert into

Related Courses

Hive Certification Course

Hadoop Course Training

All in One Data Science Course

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Independence Day Offer - Hive Training (2 Courses, 5+ Projects) Learn More