{"id":12456,"date":"2018-04-09T07:27:41","date_gmt":"2018-04-09T07:27:41","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=12456"},"modified":"2021-05-09T13:11:02","modified_gmt":"2021-05-09T07:41:02","slug":"impala-limit-clause","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/impala-limit-clause\/","title":{"rendered":"Impala LIMIT Clause – Syntax, Usage, Restrictions, Example"},"content":{"rendered":"

In our previous tutorial, we discussed\u00a0Impala HAVING Clause<\/strong>. Today, we are going to study the\u00a0Impala Limit Clause, it helps to\u00a0restrict the number of rows of a resultset to the desired number, in Impala<\/strong>. Also, we will cover its syntax, usage as well as Example to understand it well.<\/span><\/p>\n

So, let’s Impala Limit Clause.<\/p>\n

What is Impala LIMIT Clause?<\/span><\/h2>\n

Basically, in a SELECT query, this clause sets a maximum number of rows for the result set. However, while we pre-select the maximum size of the result set, \u00a0that helps Impala to optimize memory usage during processing of a distributed query.<\/span><\/p>\n

In other words, to restrict the number of rows of a resultset to the desired number the limit clause in Impala. It also specifies that the resultset of the query does not hold the records beyond the specified limit.<\/span><\/p>\n

a. Syntax<\/span><\/h3>\n

So, the syntax for using Impala LIMIT Clause is-<\/span><\/p>\n

LIMIT constant_integer_expression<\/pre>\n
Also, make sure that the argument to the LIMIT clause must evaluate to a constant value. However, \u00a0It can be a numeric literal or another kind of numeric expression. That may involve operators, casts, and function return values. But still, we cannot refer to a column or use a subquery.<\/span>
\n<\/span><\/p>\n
b. Usage<\/span><\/h3>\n
There are several contexts in which this clause is very useful. Such as:<\/span><\/p>\n
    \n
  • While we want to return exactly N items from a top-N query, we use this clause. Like the 10 highest-rated items in a shopping category or the 50 hostnames that refer the most traffic to a website.<\/span><\/li>\n
  • Also, when we want to demonstrate some sample values from a table or a particular query we use this clause. <\/span><\/li>\n
  • In order to keep queries from returning huge result sets by accident, \u00a0we use this clause. Especially, if a table is larger than expected, or a WHERE clause matches more rows than expected.<\/span><\/li>\n<\/ul>\n
    However, make sure the value for the LIMIT clause is a numeric literal. Although it can be a numeric expression but only in Impala 1.2.1 and higher.<\/span><\/p>\n
    Before Impala 1.4.0, we also needed any query including an ORDER BY clause<\/strong> to use a LIMIT clause. Also, the LIMIT clause is optional for ORDER BY queries, in Impala 1.4.0 and higher. <\/span><\/p>\n
    Though, there are some cases where sorting a huge result set requires enough memory, in order to exceed the Impala memory limit for a particular node. At that time Impala automatically uses a temporary disk work area to perform the sort operation.<\/span>
    \n<\/span>
    \nAlso, we get a provision that we can combine a LIMIT clause with an OFFSET clause to produce a small result set that is different from a top-N query.Let us understand this with an example. Here if we want to return items 11 through 20, to simulate “paged” results we can use this technique. <\/span><\/p>\n
    c. Restrictions<\/span><\/h3>\n
    However, there are some restrictions like Correlated subqueries used in EXISTS and IN operators cannot include a LIMIT clause.<\/span><\/p>\n
    Example of LIMIT Clause in Impala<\/span><\/h2>\n
    Let us suppose we have a table named Employees in the database my_db. Its contents are \u2212<\/span><\/p>\n
    [quickstart.cloudera:21000] > select * from Employees;\nQuery: select * from Employees<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n
    id<\/b><\/td>\nname<\/b><\/td>\nage<\/b><\/td>\naddress<\/b><\/td>\nsalary<\/b><\/td>\n<\/tr>\n
    3<\/b><\/td>\nkajal<\/b><\/td>\n27<\/b><\/td>\nalirajpur<\/b><\/td>\n40000<\/b><\/td>\n<\/tr>\n
    6<\/b><\/td>\nmehul<\/b><\/td>\n22<\/b><\/td>\nhyderabad<\/b><\/td>\n32000<\/b><\/td>\n<\/tr>\n
    1<\/b><\/td>\nshubham<\/b><\/td>\n32<\/b><\/td>\ndelhi<\/b><\/td>\n20000<\/b><\/td>\n<\/tr>\n
    5<\/b><\/td>\nshreyash<\/b><\/td>\n23<\/b><\/td>\npune<\/b><\/td>\n30000<\/b><\/td>\n<\/tr>\n
    2<\/b><\/td>\nmonika<\/b><\/td>\n25<\/b><\/td>\nmumbai<\/b><\/td>\n15000<\/b><\/td>\n<\/tr>\n
    8<\/b><\/td>\nRishabh<\/b><\/td>\n22<\/b><\/td>\nchennai<\/b><\/td>\n31000<\/b><\/td>\n<\/tr>\n
    9<\/b><\/td>\nPulkit<\/b><\/td>\n23<\/b><\/td>\nGandhi nagar<\/b><\/td>\n28000<\/b><\/td>\n<\/tr>\n
    7<\/b><\/td>\nVaishnavi<\/b><\/td>\n25<\/b><\/td>\nGoa<\/b><\/td>\n23000<\/b><\/td>\n<\/tr>\n
    4<\/b><\/td>\nrevti<\/b><\/td>\n25<\/b><\/td>\nindore<\/b><\/td>\n35000<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Fetched 9 row(s) in 0.51s<\/b>
    \nMoreover, \u00a0by using the order by clause we can arrange the records in the table in the ascending order of their id\u2019s.<\/span>
    \n<\/span><\/p>\n
    [quickstart.cloudera:21000] > select * from Employees order by id;\nQuery: select * from Employees order by id<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n
    id<\/b><\/td>\nname<\/b><\/td>\nage<\/b><\/td>\naddress<\/b><\/td>\nsalary<\/b><\/td>\n<\/tr>\n
    1<\/b><\/td>\nshubham<\/b><\/td>\n32<\/b><\/td>\ndelhi<\/b><\/td>\n20000<\/b><\/td>\n<\/tr>\n
    2<\/b><\/td>\nmonika<\/b><\/td>\n25<\/b><\/td>\nmumbai<\/b><\/td>\n15000<\/b><\/td>\n<\/tr>\n
    3<\/b><\/td>\nkajal<\/b><\/td>\n27<\/b><\/td>\nalirajpur<\/b><\/td>\n40000<\/b><\/td>\n<\/tr>\n
    4<\/b><\/td>\nrevti<\/b><\/td>\n25<\/b><\/td>\nindore<\/b><\/td>\n35000<\/b><\/td>\n<\/tr>\n
    5<\/b><\/td>\nshreyash<\/b><\/td>\n23<\/b><\/td>\npune<\/b><\/td>\n30000<\/b><\/td>\n<\/tr>\n
    6<\/b><\/td>\nmehul<\/b><\/td>\n22<\/b><\/td>\nhyderabad<\/b><\/td>\n32000<\/b><\/td>\n<\/tr>\n
    7<\/b><\/td>\nVaishnavi<\/b><\/td>\n25<\/b><\/td>\nGoa<\/b><\/td>\n23000<\/b><\/td>\n<\/tr>\n
    8<\/b><\/td>\nRishabh<\/b><\/td>\n22<\/b><\/td>\nchennai<\/b><\/td>\n31000<\/b><\/td>\n<\/tr>\n
    9<\/b><\/td>\nPulkit<\/b><\/td>\n23<\/b><\/td>\nGandhi nagar<\/b><\/td>\n28000<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Fetched 9 row(s) in 0.54s<\/b>
    \n<\/span>Further, \u00a0we can restrict the number of records of the output to 4, using the limit clause.<\/span>
    \n<\/span>
    \n<\/span>[quickstart.cloudera:21000] > select * from Employees order by id limit 4;<\/b>
    \n<\/span>So, we will get the following output, on executing, the above query.<\/span>
    \n<\/span>
    \n<\/span>Query: select * from Employees order by id limit 4<\/b><\/p>\n\n\n\n\n\n\n\n
    id<\/b><\/td>\nname<\/b><\/td>\nage<\/b><\/td>\naddress<\/b><\/td>\nsalary<\/b><\/td>\n<\/tr>\n
    1<\/b><\/td>\nshubham<\/b><\/td>\n32<\/b><\/td>\ndelhi<\/b><\/td>\n20000<\/b><\/td>\n<\/tr>\n
    2<\/b><\/td>\nmonika<\/b><\/td>\n25<\/b><\/td>\nmumbai<\/b><\/td>\n15000<\/b><\/td>\n<\/tr>\n
    3<\/b><\/td>\nkajal<\/b><\/td>\n27<\/b><\/td>\nalirajpur<\/b><\/td>\n40000<\/b><\/td>\n<\/tr>\n
    4<\/b><\/td>\nrevti<\/b><\/td>\n25<\/b><\/td>\nindore<\/b><\/td>\n35000<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Fetched 4 row(s) in 0.64s<\/b>
    \nSo, this was all about Impala Limit Clause. Hope you like our explanation.<\/p>\n
    Conclusion<\/span><\/h2>\n
    As a result, we have seen the whole concept of Impala LIMIT Clause. Still, if any doubt occurs in how to restrict no. of rows with Impala LIMIT Clause, feel free to ask in the comment section.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
    In our previous tutorial, we discussed\u00a0Impala HAVING Clause. Today, we are going to study the\u00a0Impala Limit Clause, it helps to\u00a0restrict the number of rows of a resultset to the desired number, in Impala. Also,...<\/p>\n","protected":false},"author":6,"featured_media":19138,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[4304,6538,11588,15187,16635],"class_list":["post-12456","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-impala","tag-example-of-limit-clause-in-imapala","tag-impala-limit-clause","tag-restrictions-impala-limit-clause","tag-usage-of-impala-limit-clause","tag-what-is-impala-limit-clause"],"yoast_head":"\nImpala LIMIT Clause - Syntax, Usage, Restrictions, Example - DataFlair<\/title>\n<meta name=\"description\" content=\"Impala LIMIT Clause - Syntax of Limit Clause in Impala, Usage of Impala Limit Clause, Restrictions & Example of Limit Clause in Imapala programming langauge\" \/>\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\/impala-limit-clause\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Impala LIMIT Clause - Syntax, Usage, Restrictions, Example - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Impala LIMIT Clause - Syntax of Limit Clause in Impala, Usage of Impala Limit Clause, Restrictions & Example of Limit Clause in Imapala programming langauge\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/impala-limit-clause\/\" \/>\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-04-09T07:27:41+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-05-09T07:41:02+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-LIMIT-Clause-01-1.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":"Impala LIMIT Clause - Syntax, Usage, Restrictions, Example - DataFlair","description":"Impala LIMIT Clause - Syntax of Limit Clause in Impala, Usage of Impala Limit Clause, Restrictions & Example of Limit Clause in Imapala programming langauge","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\/impala-limit-clause\/","og_locale":"en_US","og_type":"article","og_title":"Impala LIMIT Clause - Syntax, Usage, Restrictions, Example - DataFlair","og_description":"Impala LIMIT Clause - Syntax of Limit Clause in Impala, Usage of Impala Limit Clause, Restrictions & Example of Limit Clause in Imapala programming langauge","og_url":"https:\/\/data-flair.training\/blogs\/impala-limit-clause\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-04-09T07:27:41+00:00","article_modified_time":"2021-05-09T07:41:02+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-LIMIT-Clause-01-1.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\/impala-limit-clause\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/impala-limit-clause\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"Impala LIMIT Clause – Syntax, Usage, Restrictions, Example","datePublished":"2018-04-09T07:27:41+00:00","dateModified":"2021-05-09T07:41:02+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/impala-limit-clause\/"},"wordCount":706,"commentCount":1,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/impala-limit-clause\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-LIMIT-Clause-01-1.jpg","keywords":["Example of Limit Clause in Imapala","Impala LIMIT Clause","Restrictions impala Limit Clause","Usage of Impala Limit Clause","what is Impala LIMIT Clause"],"articleSection":["Impala Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/impala-limit-clause\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/impala-limit-clause\/","url":"https:\/\/data-flair.training\/blogs\/impala-limit-clause\/","name":"Impala LIMIT Clause - Syntax, Usage, Restrictions, Example - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/impala-limit-clause\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/impala-limit-clause\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-LIMIT-Clause-01-1.jpg","datePublished":"2018-04-09T07:27:41+00:00","dateModified":"2021-05-09T07:41:02+00:00","description":"Impala LIMIT Clause - Syntax of Limit Clause in Impala, Usage of Impala Limit Clause, Restrictions & Example of Limit Clause in Imapala programming langauge","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/impala-limit-clause\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/impala-limit-clause\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/impala-limit-clause\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-LIMIT-Clause-01-1.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-LIMIT-Clause-01-1.jpg","width":1200,"height":628,"caption":"Impala LIMIT Clause - Syntax, Usage, Restrictions, Example"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/impala-limit-clause\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Impala Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/impala\/"},{"@type":"ListItem","position":3,"name":"Impala LIMIT Clause – Syntax, Usage, Restrictions, Example"}]},{"@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\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/12456","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\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=12456"}],"version-history":[{"count":1,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/12456\/revisions"}],"predecessor-version":[{"id":94039,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/12456\/revisions\/94039"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/19138"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=12456"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=12456"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=12456"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}