{"id":10308,"date":"2018-03-08T12:13:33","date_gmt":"2018-03-08T12:13:33","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=10308"},"modified":"2018-03-08T12:13:33","modified_gmt":"2018-03-08T12:13:33","slug":"hiveql-order-by-query","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/","title":{"rendered":"HiveQL Select – Order By Query"},"content":{"rendered":"

In last\u00a0Apache Hive Tutorial<\/strong>, we study Hive<\/strong>\u00a0Group By Query<\/strong> in detail, in this tutorial we study to retrieve the details based on one column and sort the result set by ascending or descending order we use the HiveQL Select – Order By query\/ clause with “SELECT” statement in Hive query. <\/span><\/p>\n

However, we need to know Hive Order By query syntax properly to use it. So, in this Hive Order By Clause article, we will learn the whole concept of HiveQL Select- Order By Query. But before HiveQL Select Order By query let\u2019s learn brief introduction to Hive Query. <\/span><\/p>\n

Further, we will learn example of Hive Order By query, JDBC Program with an output of Hive Order By query.<\/span><\/p>\n

What is Hive Query<\/span><\/h2>\n

Basically, for the ETL purpose on top of Hadoop file system Hive offers SQL type querying language.\u00a0<\/span>However, to be more specific, to work with tables, databases, queries in Hive Hive Query language (HiveQL) offers SQL type environment.<\/span>
\n<\/span>
\n<\/span>In addition, \u00a0to perform different type data manipulations and querying it is possible to have a different type of Clauses associated with Hive.<\/span>\u00a0Also, provides better connectivity with different nodes outside the environment. Moreover, Hive offers JDBC connectivity as well.
\n<\/span>
\n<\/span>Moreover, there are following features Hive queries offers.<\/span> Such as:<\/p>\n

    \n
  • At first, data modeling. For example, a creation of databases, tables, etc.<\/span><\/li>\n
  • Also, ETL functionalities.\u00a0For example, Extraction, Transformation, and Loading data into tables<\/span><\/li>\n
  • Moreover, it offers Joins to merge different data tables<\/span><\/li>\n
  • However, for ease of code, it offers user-specific custom scripts.<\/span><\/li>\n
  • Also, provides a faster-querying tool on top of Hadoop<\/strong><\/span><\/li>\n<\/ul>\n

    HiveQL Select – Order By Query<\/span><\/h2>\n

    Basically, HiveQL Select Order By Q<\/span>uery\u00a0<\/span>syntax is as same as the syntax of ORDER BY Query in SQL language.\u00a0<\/span>Moreover, in Hive queries, we use Order by clause with Hive Select Statement<\/strong>. However, that guarantees total ordering of data. <\/span><\/p>\n

    Also, for grouping particular column values mentioned in Order by clause use columns on Hive tables. In addition, the order by clause the query will selects and display results in ascending or descending order the particular column values, for whatever the column name we are defining.<\/span><\/p>\n

    i.\u00a0<\/span>Order By Query\u00a0<\/span>Syntax<\/span><\/h3>\n

    However, here is the syntax of the ORDER BY Clause:<\/span><\/p>\n

    SELECT [ALL | DISTINCT] select_expr, select_expr, ...\nFROM table_reference\n[WHERE where_condition]\n[GROUP BY col_list]\n[HAVING having_condition]\n[ORDER BY col_list]]\n[LIMIT number];<\/pre>\n
    ii. Example of Order By Clause<\/span><\/h3>\n
    Further, to understand well let\u2019s suppose employee table as given below. Basically, it includes the fields named Id, Name, Salary, Designation, and Dept. Moreover, to retrieve the employee details in Order by Query using Department name, generate a query.<\/span>
    \nTablea.1 Order By Query<\/strong><\/p>\n\n\n\n\n\n\n\n\n
    ID <\/strong><\/td>\nName<\/strong><\/td>\nSalary<\/strong><\/td>\nDesignation<\/strong><\/td>\n Dept<\/strong><\/td>\n<\/tr>\n
    1201<\/span><\/td>\nRoss<\/span><\/td>\n45000<\/span><\/td>\nTechnical manager<\/span><\/td>\nTP<\/span><\/td>\n<\/tr>\n
    1202<\/span><\/td>\nRachel<\/span><\/td>\n45000<\/span><\/td>\n Proofreader <\/span><\/td>\nPR<\/span><\/td>\n<\/tr>\n
    1203<\/span><\/td>\nChandler<\/span><\/td>\n40000<\/span><\/td>\nTechnical writer<\/span><\/td>\nTP<\/span><\/td>\n<\/tr>\n
    1204<\/span><\/td>\nMike<\/span><\/td>\n40000<\/span><\/td>\nHR Admin<\/span><\/td>\nHR<\/span><\/td>\n<\/tr>\n
    1205<\/span><\/td>\nPhoebe<\/span><\/td>\n30000<\/span><\/td>\nOp Admin<\/span><\/td>\nAdmin<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    However, using the above scenario the following query retrieves the employee details:
    \nhive> SELECT Id, Name, Dept FROM employee ORDER BY DEPT;<\/span><\/p>\n
    Also, we get to see the following response to the successful execution of the query:<\/span><\/p>\n
    Table.2 Order By Clause<\/strong><\/p>\n\n\n\n\n\n\n\n\n
    ID <\/strong><\/td>\nName<\/strong><\/td>\nSalary<\/strong><\/td>\nDesignation<\/strong><\/td>\n Dept<\/strong><\/td>\n<\/tr>\n
    1205<\/span><\/td>\nPhoebe<\/span><\/td>\n30000<\/span><\/td>\nOp Admin<\/span><\/td>\nAdmin<\/span><\/td>\n<\/tr>\n
    1204<\/span><\/td>\nMike<\/span><\/td>\n40000<\/span><\/td>\nHR Admin<\/span><\/td>\nHR<\/span><\/td>\n<\/tr>\n
    1202<\/span><\/td>\nRachel<\/span><\/td>\n45000<\/span><\/td>\nProofreader<\/span><\/td>\nPr<\/span><\/td>\n<\/tr>\n
    1201<\/span><\/td>\nRoss<\/span><\/td>\n45000<\/span><\/td>\nTechnical manager<\/span><\/td>\nTP<\/span><\/td>\n<\/tr>\n
    1203<\/span><\/td>\nChandler<\/span><\/td>\n40000<\/span><\/td>\nTechnical writer<\/span><\/td>\nTP<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    iii. JDBC Program<\/h3>\n
    Basically, to apply for Order By clause here is the JDBC program for the given example.<\/span><\/p>\n
    import java.sql.SQLException;\nimport java.sql.Connection;\nimport java.sql.ResultSet;\nimport java.sql.Statement;\nimport java.sql.DriverManager;\npublic class HiveQLOrderBy\n  {\n\u00a0\u00a0   private static String driverName = \"org.apache.hadoop.hive.jdbc.HiveDriver\";\u00a0\n\u00a0\u00a0   public static void main(String[] args) throws SQLException\n        {\n\/\/ Register driver and create driver instance\n\u00a0\u00a0\u00a0\u00a0\u00a0       Class.forName(driverName);\u00a0\u00a0\u00a0\n\/\/ get connection\n\u00a0\u00a0\u00a0\u00a0\u00a0       Connection con = DriverManager.getConnection(\"jdbc:hive:\/\/localhost:10000\/userdb\", \"\", \"\");\u00a0\u00a0\n\/\/ create statement\n\u00a0\u00a0\u00a0\u00a0\u00a0       Statement stmt = con.createStatement();\u00a0\u00a0\u00a0\n\/\/ execute statement\n\u00a0\u00a0\u00a0\u00a0\u00a0       Resultset res = stmt.executeQuery(\"SELECT * FROM employee ORDER BY DEPT;\");\n\u00a0\u00a0\u00a0\u00a0       \u00a0System.out.println(\" ID \\t Name \\t Salary \\t Designation \\t Dept \");\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0       while (res.next())\n              {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0              System.out.println(res.getInt(1) + \" \" + res.getString(2) + \" \" + res.getDouble(3) + \" \" + res.getString(4) + \" \" + res.getString(5));\n\u00a0\u00a0\u00a0\u00a0         \u00a0}\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0       con.close();\n\u00a0\u00a0       }\n  }<\/pre>\n
    Moreover, by Using the following commands to compile and execute this program. Also, save the program in a file named HiveQLOrderBy.java.<\/span>
    \n$ javac HiveQLOrderBy.java<\/span>
    \n$ java HiveQLOrderBy<\/span><\/p>\n
    iv.\u00a0<\/span>Order By Query –\u00a0<\/span>Output<\/span><\/h3>\n
    Table.3 Order By Clause – Output<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n
    ID <\/strong><\/td>\nName<\/strong><\/td>\nSalary<\/strong><\/td>\nDesignation<\/strong><\/td>\n Dept<\/strong><\/td>\n<\/tr>\n
    1205<\/span><\/td>\nPhoebe<\/span><\/td>\n30000<\/span><\/td>\nOp Admin<\/span><\/td>\nAdmin<\/span><\/td>\n<\/tr>\n
    1204<\/span><\/td>\nMike<\/span><\/td>\n40000<\/span><\/td>\nHR Admin<\/span><\/td>\nHR<\/span><\/td>\n<\/tr>\n
    1202<\/span><\/td>\nRachel<\/span><\/td>\n45000<\/span><\/td>\nProofreader<\/span><\/td>\nPr<\/span><\/td>\n<\/tr>\n
    1201<\/span><\/td>\nRoss<\/span><\/td>\n45000<\/span><\/td>\nTechnical manager<\/span><\/td>\nTP<\/span><\/td>\n<\/tr>\n
    1203<\/span><\/td>\nChandler<\/span><\/td>\n40000<\/span><\/td>\nTechnical writer<\/span><\/td>\nTP<\/span><\/td>\n<\/tr>\n
    1204<\/span><\/td>\nMike<\/span><\/td>\n40000<\/span><\/td>\nHR Admin<\/span><\/td>\nHR<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    This was all about HiveQL Select – Hive Order By Query Tutorial. Hope you like our explanation of Hive Order By Clause.<\/div>\n
    Conclusion<\/h2>\n
    As a result, we have seen what is HiveQL Select – Order by Query\/ Order by Clause. Moreover, we will study\u00a0<\/span>example as well as syntax of Order By Query with JDBC Program. Still, if you have any query, feel free to ask in the comment section.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
    In last\u00a0Apache Hive Tutorial, we study Hive\u00a0Group By Query in detail, in this tutorial we study to retrieve the details based on one column and sort the result set by ascending or descending order...<\/p>\n","protected":false},"author":7,"featured_media":10595,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[4311,5712,5761,5762,5814,5817,7773,9323,9325,15212,15838],"class_list":["post-10308","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hive","tag-example-of-order-by-query","tag-hive-group-by","tag-hive-order-by-limit","tag-hive-order-by-nulls-last","tag-hiveql-order-by-clause","tag-hiveql-select-orderby","tag-jdbc-program","tag-order-by-in-sql","tag-order-by-query-in-hiveql","tag-use-of-sort-by-in-hive","tag-what-is-order-by-query"],"yoast_head":"\nHiveQL Select - Order By Query - DataFlair<\/title>\n<meta name=\"description\" content=\"HiveQL Select statement-Order By Query, Order By Clause syntax, order by Clause example,JDBC Program,Order By Query output, Hive Order by count, hive query,\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"HiveQL Select - Order By Query - DataFlair\" \/>\n<meta property=\"og:description\" content=\"HiveQL Select statement-Order By Query, Order By Clause syntax, order by Clause example,JDBC Program,Order By Query output, Hive Order by count, hive query,\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-08T12:13:33+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-OrderBy-query-01.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"HiveQL Select - Order By Query - DataFlair","description":"HiveQL Select statement-Order By Query, Order By Clause syntax, order by Clause example,JDBC Program,Order By Query output, Hive Order by count, hive query,","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/","og_locale":"en_US","og_type":"article","og_title":"HiveQL Select - Order By Query - DataFlair","og_description":"HiveQL Select statement-Order By Query, Order By Clause syntax, order by Clause example,JDBC Program,Order By Query output, Hive Order by count, hive query,","og_url":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-03-08T12:13:33+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-OrderBy-query-01.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd"},"headline":"HiveQL Select – Order By Query","datePublished":"2018-03-08T12:13:33+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/"},"wordCount":675,"commentCount":1,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-OrderBy-query-01.jpg","keywords":["example of order By Query","hive group by","hive order by limit","hive order by nulls last","HiveQL order by clause","HiveQL Select OrderBy","JDBC Program","order by in sql","order By Query in HiveQL","use of sort by in hive","what is order By Query"],"articleSection":["Hive Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/","url":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/","name":"HiveQL Select - Order By Query - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-OrderBy-query-01.jpg","datePublished":"2018-03-08T12:13:33+00:00","description":"HiveQL Select statement-Order By Query, Order By Clause syntax, order by Clause example,JDBC Program,Order By Query output, Hive Order by count, hive query,","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-OrderBy-query-01.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/HiveQL-Select-OrderBy-query-01.jpg","width":1200,"height":628,"caption":"Hive Order By Query | Order By Cause"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/hiveql-order-by-query\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Hive Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/hive\/"},{"@type":"ListItem","position":3,"name":"HiveQL Select – Order By Query"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"DataFlair Team specializes in creating clear, actionable content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Backed by industry expertise, we make learning easy and career-oriented for beginners and pros alike.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam3\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/10308","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=10308"}],"version-history":[{"count":0,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/10308\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/10595"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=10308"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=10308"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=10308"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}