🕷️ Crawler Inspector

URL Lookup

Direct Parameter Lookup

Raw Queries and Responses

1. Shard Calculation

Query:

Response:

Calculated Shard: 89 (from laksa085)

2. Crawled Status Check

Query:

curl -X POST \
  'http://laksa089.int.ahrefs:8124/' \
  -H 'Content-Type: text/plain' \
  -H 'X-ClickHouse-Database: crawler3' \
  -H 'Authorization: Basic YXBpOg==' \
  -d 'SELECT getAhrefsURLFromUnparsed(src_unparsed) AS found_url, ifNull(toUnixTimestamp(download_stamp), 0) AS crawl_time, ifNull(toUnixTimestamp(props_url_first_seen), 0) AS first_indexed_time, download_http_code AS http_code, src_unparsed AS src_unparsed, src_root_hash AS src_root_hash, history_drop_reason AS history_drop_reason, meta_title AS meta_title, meta_descriptions AS meta_descriptions, attrs_boilerpipe_text AS attrs_boilerpipe_text, attrs_markdown AS attrs_markdown, attrs_readable_markdown AS attrs_readable_markdown, meta_canonical AS meta_canonical FROM crawler3.page_info_local FINAL PREWHERE (src_root_hash, src_unparsed) IN ((getAhrefsRootHashFromUnparsed(getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/sql-reference/statements/select/limit-by\')), getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/sql-reference/statements/select/limit-by\'))) FORMAT JSONEachRow'

Response:

{"found_url":"https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by","crawl_time":1775603289,"first_indexed_time":1740105108,"http_code":200,"src_unparsed":"com,clickhouse!\/docs\/sql-reference\/statements\/select\/limit-by s443","src_root_hash":"12633450985039531489","history_drop_reason":null,"meta_title":"LIMIT BY Clause | ClickHouse Docs","meta_descriptions":["Documentation for LIMIT BY Clause"],"attrs_boilerpipe_text":"A query with the\nLIMIT n BY expressions\nclause selects the first\nn\nrows for each distinct value of\nexpressions\n. The key for\nLIMIT BY\ncan contain any number of\nexpressions\n.\nClickHouse supports the following syntax variants:\nLIMIT [offset_value, ]n BY expressions\nLIMIT n OFFSET offset_value BY expressions\nDuring query processing, ClickHouse selects data ordered by sorting key. The sorting key is set explicitly using an\nORDER BY\nclause or implicitly as a property of the table engine (row order is only guaranteed when using\nORDER BY\n, otherwise the row blocks will not be ordered due to multi-threading). Then ClickHouse applies\nLIMIT n BY expressions\nand returns the first\nn\nrows for each distinct combination of\nexpressions\n. If\nOFFSET\nis specified, then for each data block that belongs to a distinct combination of\nexpressions\n, ClickHouse skips\noffset_value\nnumber of rows from the beginning of the block and returns a maximum of\nn\nrows as a result. If\noffset_value\nis bigger than the number of rows in the data block, ClickHouse returns zero rows from the block.\nNote\nLIMIT BY\nis not related to\nLIMIT\n. They can both be used in the same query.\nIf you want to use column numbers instead of column names in the\nLIMIT BY\nclause, enable the setting\nenable_positional_arguments\n.\nExamples\n\nSample table:\nCREATE\nTABLE\nlimit_by\n(\nid\nInt\n,\nval\nInt\n)\nENGINE\n=\nMemory\n;\nINSERT\nINTO\nlimit_by\nVALUES\n(\n1\n,\n10\n)\n,\n(\n1\n,\n11\n)\n,\n(\n1\n,\n12\n)\n,\n(\n2\n,\n20\n)\n,\n(\n2\n,\n21\n)\n;\nQueries:\nSELECT\n*\nFROM\nlimit_by\nORDER\nBY\nid\n,\nval\nLIMIT\n2\nBY\nid\n┌─id─┬─val─┐\n│  1 │  10 │\n│  1 │  11 │\n│  2 │  20 │\n│  2 │  21 │\n└────┴─────┘\nSELECT\n*\nFROM\nlimit_by\nORDER\nBY\nid\n,\nval\nLIMIT\n1\n,\n2\nBY\nid\n┌─id─┬─val─┐\n│  1 │  11 │\n│  1 │  12 │\n│  2 │  21 │\n└────┴─────┘\nThe\nSELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id\nquery returns the same result.\nThe following query returns the top 5 referrers for each\ndomain, device_type\npair with a maximum of 100 rows in total (\nLIMIT n BY + LIMIT\n).\nSELECT\ndomainWithoutWWW\n(\nURL\n)\nAS\ndomain\n,\ndomainWithoutWWW\n(\nREFERRER_URL\n)\nAS\nreferrer\n,\ndevice_type\n,\ncount\n(\n)\ncnt\nFROM\nhits\nGROUP\nBY\ndomain\n,\nreferrer\n,\ndevice_type\nORDER\nBY\ncnt\nDESC\nLIMIT\n5\nBY\ndomain\n,\ndevice_type\nLIMIT\n100\nLIMIT BY ALL\n\nLIMIT BY ALL\nis equivalent to listing all the SELECT-ed expressions that are not aggregate functions.\nFor example:\nSELECT\ncol1\n,\ncol2\n,\ncol3\nFROM\ntable\nLIMIT\n2\nBY\nALL\nis the same as\nSELECT\ncol1\n,\ncol2\n,\ncol3\nFROM\ntable\nLIMIT\n2\nBY\ncol1\n,\ncol2\n,\ncol3\nFor a special case that if there is a function having both aggregate functions and other fields as its arguments, the\nLIMIT BY\nkeys will contain the maximum non-aggregate fields we can extract from it.\nFor example:\nSELECT\nsubstring\n(\na\n,\n4\n,\n2\n)\n,\nsubstring\n(\nsubstring\n(\na\n,\n1\n,\n2\n)\n,\n1\n,\ncount\n(\nb\n)\n)\nFROM\nt\nLIMIT\n2\nBY\nALL\nis the same as\nSELECT\nsubstring\n(\na\n,\n4\n,\n2\n)\n,\nsubstring\n(\nsubstring\n(\na\n,\n1\n,\n2\n)\n,\n1\n,\ncount\n(\nb\n)\n)\nFROM\nt\nLIMIT\n2\nBY\nsubstring\n(\na\n,\n4\n,\n2\n)\n,\nsubstring\n(\na\n,\n1\n,\n2\n)\nExamples\n\nSample table:\nCREATE\nTABLE\nlimit_by\n(\nid\nInt\n,\nval\nInt\n)\nENGINE\n=\nMemory\n;\nINSERT\nINTO\nlimit_by\nVALUES\n(\n1\n,\n10\n)\n,\n(\n1\n,\n11\n)\n,\n(\n1\n,\n12\n)\n,\n(\n2\n,\n20\n)\n,\n(\n2\n,\n21\n)\n;\nQueries:\nSELECT\n*\nFROM\nlimit_by\nORDER\nBY\nid\n,\nval\nLIMIT\n2\nBY\nid\n┌─id─┬─val─┐\n│  1 │  10 │\n│  1 │  11 │\n│  2 │  20 │\n│  2 │  21 │\n└────┴─────┘\nSELECT\n*\nFROM\nlimit_by\nORDER\nBY\nid\n,\nval\nLIMIT\n1\n,\n2\nBY\nid\n┌─id─┬─val─┐\n│  1 │  11 │\n│  1 │  12 │\n│  2 │  21 │\n└────┴─────┘\nThe\nSELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id\nquery returns the same result.\nUsing\nLIMIT BY ALL\n:\nSELECT\nid\n,\nval\nFROM\nlimit_by\nORDER\nBY\nid\n,\nval\nLIMIT\n2\nBY\nALL\nThis is equivalent to:\nSELECT\nid\n,\nval\nFROM\nlimit_by\nORDER\nBY\nid\n,\nval\nLIMIT\n2\nBY\nid\n,\nval\nThe following query returns the top 5 referrers for each\ndomain, device_type\npair with a maximum of 100 rows in total (\nLIMIT n BY + LIMIT\n).\nSELECT\ndomainWithoutWWW\n(\nURL\n)\nAS\ndomain\n,\ndomainWithoutWWW\n(\nREFERRER_URL\n)\nAS\nreferrer\n,\ndevice_type\n,\ncount\n(\n)\ncnt\nFROM\nhits\nGROUP\nBY\ndomain\n,\nreferrer\n,\ndevice_type\nORDER\nBY\ncnt\nDESC\nLIMIT\n5\nBY\ndomain\n,\ndevice_type\nLIMIT\n100","attrs_markdown":"[Skip to main content](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.7k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=66b0bd70-8178-4af1-8668-a09558d0d04d&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&utm_ga=GA1.1.510657712.1775603292)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=66b0bd70-8178-4af1-8668-a09558d0d04d&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&utm_ga=GA1.1.510657712.1775603292)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n\n- [English](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/sql-reference\/statements\/select\/limit-by)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/sql-reference\/statements\/select\/limit-by)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/sql-reference\/statements\/select\/limit-by)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/sql-reference\/statements\/select\/limit-by)\n\n[Skip to main content](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.7k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=66b0bd70-8178-4af1-8668-a09558d0d04d&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&utm_ga=GA1.1.510657712.1775603292)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=66b0bd70-8178-4af1-8668-a09558d0d04d&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&utm_ga=GA1.1.510657712.1775603292)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n\n- [English](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/sql-reference\/statements\/select\/limit-by)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/sql-reference\/statements\/select\/limit-by)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/sql-reference\/statements\/select\/limit-by)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/sql-reference\/statements\/select\/limit-by)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n- [Introduction](https:\/\/clickhouse.com\/docs\/sql-reference)\n  - [Syntax](https:\/\/clickhouse.com\/docs\/sql-reference\/syntax)\n  - [Input and Output Formats](https:\/\/clickhouse.com\/docs\/sql-reference\/formats)\n  - [Data types](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types)\n  - [Statements](https:\/\/clickhouse.com\/docs\/sql-reference\/statements)\n    - [SELECT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n      - [ALL](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/all)\n      - [APPLY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/apply-modifier)\n      - [ARRAY JOIN](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/array-join)\n      - [DISTINCT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/distinct)\n      - [EXCEPT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/except)\n      - [EXCEPT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/except-modifier)\n      - [FORMAT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/format)\n      - [FROM](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/from)\n      - [GROUP BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/group-by)\n      - [HAVING](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/having)\n      - [INTERSECT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/intersect)\n      - [INTO OUTFILE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/into-outfile)\n      - [JOIN](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/join)\n      - [LIMIT BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n      - [LIMIT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit)\n      - [OFFSET](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/offset)\n      - [ORDER BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/order-by)\n      - [PREWHERE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/prewhere)\n      - [QUALIFY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/qualify)\n      - [REPLACE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/replace-modifier)\n      - [SAMPLE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/sample)\n      - [UNION](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/union)\n      - [WHERE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/where)\n      - [WITH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/with)\n    - [INSERT INTO](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/insert-into)\n    - [CREATE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/create)\n    - [ALTER](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/alter)\n    - [DELETE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/delete)\n    - [SYSTEM](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/system)\n    - [SHOW](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/show)\n    - [GRANT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/grant)\n    - [EXPLAIN](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/explain)\n    - [REVOKE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/revoke)\n    - [UPDATE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/update)\n    - [ATTACH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/attach)\n    - [CHECK TABLE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/check-table)\n    - [DESCRIBE TABLE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/describe-table)\n    - [DETACH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/detach)\n    - [DROP](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/drop)\n    - [EXISTS](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/exists)\n    - [KILL](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/kill)\n    - [OPTIMIZE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/optimize)\n    - [RENAME](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/rename)\n    - [EXCHANGE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/exchange)\n    - [SET](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/set)\n    - [SET ROLE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/set-role)\n    - [TRUNCATE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/truncate)\n    - [EXECUTE AS](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/execute_as)\n    - [PARALLEL WITH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/parallel_with)\n    - [USE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/use)\n    - [WATCH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/watch)\n    - [MOVE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/move)\n    - [CHECK GRANT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/check-grant)\n    - [UNDROP](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/undrop)\n  - [Operators](https:\/\/clickhouse.com\/docs\/sql-reference\/operators)\n- [Engines](https:\/\/clickhouse.com\/docs\/engines)\n  - [Database Engines](https:\/\/clickhouse.com\/docs\/engines\/database-engines)\n  - [Table Engines](https:\/\/clickhouse.com\/docs\/engines\/table-engines)\n- [Functions](https:\/\/clickhouse.com\/docs\/sql-reference\/functions)\n  - [Regular functions](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/regular-functions)\n  - [Aggregate functions](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions)\n  - [Table functions](https:\/\/clickhouse.com\/docs\/sql-reference\/table-functions)\n  - [Window functions](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n- [Formats](https:\/\/clickhouse.com\/docs\/interfaces\/formats)\n- [Data Lakes](https:\/\/clickhouse.com\/docs\/sql-reference\/datalakes)\n\n- [Introduction](https:\/\/clickhouse.com\/docs\/sql-reference)\n- [Statements](https:\/\/clickhouse.com\/docs\/sql-reference\/statements)\n- [SELECT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select)\n- LIMIT BY\n\n[Edit this page](https:\/\/github.com\/ClickHouse\/ClickHouse\/tree\/master\/docs\/en\/sql-reference\/statements\/select\/limit-by.md)\n\n# LIMIT BY Clause\nA query with the `LIMIT n BY expressions` clause selects the first `n` rows for each distinct value of `expressions`. The key for `LIMIT BY` can contain any number of [expressions](https:\/\/clickhouse.com\/docs\/sql-reference\/syntax#expressions).\n\nClickHouse supports the following syntax variants:\n\n- `LIMIT [offset_value, ]n BY expressions`\n- `LIMIT n OFFSET offset_value BY expressions`\n\nDuring query processing, ClickHouse selects data ordered by sorting key. The sorting key is set explicitly using an [ORDER BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/order-by) clause or implicitly as a property of the table engine (row order is only guaranteed when using [ORDER BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/order-by), otherwise the row blocks will not be ordered due to multi-threading). Then ClickHouse applies `LIMIT n BY expressions` and returns the first `n` rows for each distinct combination of `expressions`. If `OFFSET` is specified, then for each data block that belongs to a distinct combination of `expressions`, ClickHouse skips `offset_value` number of rows from the beginning of the block and returns a maximum of `n` rows as a result. If `offset_value` is bigger than the number of rows in the data block, ClickHouse returns zero rows from the block.\n\nNote\n\n`LIMIT BY` is not related to [LIMIT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit). They can both be used in the same query.\n\nIf you want to use column numbers instead of column names in the `LIMIT BY` clause, enable the setting [enable\\_positional\\_arguments](https:\/\/clickhouse.com\/docs\/operations\/settings\/settings#enable_positional_arguments).\n\n## Examples[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by#examples \"Direct link to Examples\")\nSample table:\n```\nCREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;\nINSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);\n```\nQueries:\n```\nSELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id\n```\n```\n┌─id─┬─val─┐\n│  1 │  10 │\n│  1 │  11 │\n│  2 │  20 │\n│  2 │  21 │\n└────┴─────┘\n```\n```\nSELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id\n```\n```\n┌─id─┬─val─┐\n│  1 │  11 │\n│  1 │  12 │\n│  2 │  21 │\n└────┴─────┘\n```\nThe `SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id` query returns the same result.\n\nThe following query returns the top 5 referrers for each `domain, device_type` pair with a maximum of 100 rows in total (`LIMIT n BY + LIMIT`).\n```\nSELECT\n    domainWithoutWWW(URL) AS domain,\n    domainWithoutWWW(REFERRER_URL) AS referrer,\n    device_type,\n    count() cnt\nFROM hits\nGROUP BY domain, referrer, device_type\nORDER BY cnt DESC\nLIMIT 5 BY domain, device_type\nLIMIT 100\n```\n## LIMIT BY ALL[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by#limit-by-all \"Direct link to LIMIT BY ALL\")\n`LIMIT BY ALL` is equivalent to listing all the SELECT-ed expressions that are not aggregate functions.\n\nFor example:\n```\nSELECT col1, col2, col3 FROM table LIMIT 2 BY ALL\n```\nis the same as\n```\nSELECT col1, col2, col3 FROM table LIMIT 2 BY col1, col2, col3\n```\nFor a special case that if there is a function having both aggregate functions and other fields as its arguments, the `LIMIT BY` keys will contain the maximum non-aggregate fields we can extract from it.\n\nFor example:\n```\nSELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY ALL\n```\nis the same as\n```\nSELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY substring(a, 4, 2), substring(a, 1, 2)\n```\n## Examples[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by#examples-limit-by-all \"Direct link to Examples\")\nSample table:\n```\nCREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;\nINSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);\n```\nQueries:\n```\nSELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id\n```\n```\n┌─id─┬─val─┐\n│  1 │  10 │\n│  1 │  11 │\n│  2 │  20 │\n│  2 │  21 │\n└────┴─────┘\n```\n```\nSELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id\n```\n```\n┌─id─┬─val─┐\n│  1 │  11 │\n│  1 │  12 │\n│  2 │  21 │\n└────┴─────┘\n```\nThe `SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id` query returns the same result.\n\nUsing `LIMIT BY ALL`:\n```\nSELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY ALL\n```\nThis is equivalent to:\n```\nSELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY id, val\n```\nThe following query returns the top 5 referrers for each `domain, device_type` pair with a maximum of 100 rows in total (`LIMIT n BY + LIMIT`).\n```\nSELECT\n    domainWithoutWWW(URL) AS domain,\n    domainWithoutWWW(REFERRER_URL) AS referrer,\n    device_type,\n    count() cnt\nFROM hits\nGROUP BY domain, referrer, device_type\nORDER BY cnt DESC\nLIMIT 5 BY domain, device_type\nLIMIT 100\n```\n\n[Previous JOIN](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/join)\n\n[Next LIMIT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit)\n\n- [Examples](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by#examples)\n- [LIMIT BY ALL](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by#limit-by-all)\n- [Examples](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by#examples-limit-by-all)\n\nWas this page helpful?\n\n###### Try ClickHouse Cloud for FREE\nSeparation of storage and compute, automatic scaling, built-in SQL console, and lots more. \\$300 in free credits when signing up.\n\n[Try it for Free](https:\/\/console.clickhouse.cloud\/signUp?loc=doc-card-banner&glxid=66b0bd70-8178-4af1-8668-a09558d0d04d&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&utm_ga=GA1.1.510657712.1775603292)\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\nEN\n\n- Get started▼\n- Cloud▼\n- Manage data▼\n- Server admin▼\n- Reference▼\n- Integrations▼\n- ClickStack▼\n- chDB▼\n- About▼\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\nEN\n\nmain-menu\n\n- Introduction▼\n  - [Syntax](https:\/\/clickhouse.com\/docs\/sql-reference\/syntax)\n  - [Input and Output Formats](https:\/\/clickhouse.com\/docs\/sql-reference\/formats)\n  - Data types▼\n  - Statements▼\n    - SELECT▼\n      - [ALL](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/all)\n      - [APPLY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/apply-modifier)\n      - [ARRAY JOIN](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/array-join)\n      - [DISTINCT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/distinct)\n      - [EXCEPT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/except)\n      - [EXCEPT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/except-modifier)\n      - [FORMAT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/format)\n      - [FROM](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/from)\n      - [GROUP BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/group-by)\n      - [HAVING](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/having)\n      - [INTERSECT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/intersect)\n      - [INTO OUTFILE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/into-outfile)\n      - [JOIN](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/join)\n      - [LIMIT BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by)\n      - [LIMIT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit)\n      - [OFFSET](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/offset)\n      - [ORDER BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/order-by)\n      - [PREWHERE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/prewhere)\n      - [QUALIFY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/qualify)\n      - [REPLACE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/replace-modifier)\n      - [SAMPLE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/sample)\n      - [UNION](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/union)\n      - [WHERE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/where)\n      - [WITH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/with)\n    - [INSERT INTO](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/insert-into)\n    - CREATE▼\n    - ALTER▼\n    - [DELETE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/delete)\n    - [SYSTEM](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/system)\n    - [SHOW](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/show)\n    - [GRANT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/grant)\n    - [EXPLAIN](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/explain)\n    - [REVOKE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/revoke)\n    - [UPDATE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/update)\n    - [ATTACH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/attach)\n    - [CHECK TABLE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/check-table)\n    - [DESCRIBE TABLE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/describe-table)\n    - [DETACH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/detach)\n    - [DROP](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/drop)\n    - [EXISTS](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/exists)\n    - [KILL](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/kill)\n    - [OPTIMIZE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/optimize)\n    - [RENAME](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/rename)\n    - [EXCHANGE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/exchange)\n    - [SET](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/set)\n    - [SET ROLE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/set-role)\n    - [TRUNCATE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/truncate)\n    - [EXECUTE AS](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/execute_as)\n    - [PARALLEL WITH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/parallel_with)\n    - [USE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/use)\n    - [WATCH](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/watch)\n    - [MOVE](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/move)\n    - [CHECK GRANT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/check-grant)\n    - [UNDROP](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/undrop)\n  - Operators▼\n- Engines▼\n- Functions▼\n- Formats▼\n- [Data Lakes](https:\/\/clickhouse.com\/docs\/sql-reference\/datalakes)","attrs_readable_markdown":"A query with the `LIMIT n BY expressions` clause selects the first `n` rows for each distinct value of `expressions`. The key for `LIMIT BY` can contain any number of [expressions](https:\/\/clickhouse.com\/docs\/sql-reference\/syntax#expressions).\n\nClickHouse supports the following syntax variants:\n\n- `LIMIT [offset_value, ]n BY expressions`\n- `LIMIT n OFFSET offset_value BY expressions`\n\nDuring query processing, ClickHouse selects data ordered by sorting key. The sorting key is set explicitly using an [ORDER BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/order-by) clause or implicitly as a property of the table engine (row order is only guaranteed when using [ORDER BY](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/order-by), otherwise the row blocks will not be ordered due to multi-threading). Then ClickHouse applies `LIMIT n BY expressions` and returns the first `n` rows for each distinct combination of `expressions`. If `OFFSET` is specified, then for each data block that belongs to a distinct combination of `expressions`, ClickHouse skips `offset_value` number of rows from the beginning of the block and returns a maximum of `n` rows as a result. If `offset_value` is bigger than the number of rows in the data block, ClickHouse returns zero rows from the block.\n\nNote\n\n`LIMIT BY` is not related to [LIMIT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit). They can both be used in the same query.\n\nIf you want to use column numbers instead of column names in the `LIMIT BY` clause, enable the setting [enable\\_positional\\_arguments](https:\/\/clickhouse.com\/docs\/operations\/settings\/settings#enable_positional_arguments).\n\n## Examples[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by#examples \"Direct link to Examples\")\nSample table:\n```\nCREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;\nINSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);\n```\nQueries:\n```\nSELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id\n```\n```\n┌─id─┬─val─┐\n│  1 │  10 │\n│  1 │  11 │\n│  2 │  20 │\n│  2 │  21 │\n└────┴─────┘\n```\n```\nSELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id\n```\n```\n┌─id─┬─val─┐\n│  1 │  11 │\n│  1 │  12 │\n│  2 │  21 │\n└────┴─────┘\n```\nThe `SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id` query returns the same result.\n\nThe following query returns the top 5 referrers for each `domain, device_type` pair with a maximum of 100 rows in total (`LIMIT n BY + LIMIT`).\n```\nSELECT\n    domainWithoutWWW(URL) AS domain,\n    domainWithoutWWW(REFERRER_URL) AS referrer,\n    device_type,\n    count() cnt\nFROM hits\nGROUP BY domain, referrer, device_type\nORDER BY cnt DESC\nLIMIT 5 BY domain, device_type\nLIMIT 100\n```\n## LIMIT BY ALL[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by#limit-by-all \"Direct link to LIMIT BY ALL\")\n`LIMIT BY ALL` is equivalent to listing all the SELECT-ed expressions that are not aggregate functions.\n\nFor example:\n```\nSELECT col1, col2, col3 FROM table LIMIT 2 BY ALL\n```\nis the same as\n```\nSELECT col1, col2, col3 FROM table LIMIT 2 BY col1, col2, col3\n```\nFor a special case that if there is a function having both aggregate functions and other fields as its arguments, the `LIMIT BY` keys will contain the maximum non-aggregate fields we can extract from it.\n\nFor example:\n```\nSELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY ALL\n```\nis the same as\n```\nSELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY substring(a, 4, 2), substring(a, 1, 2)\n```\n## Examples[](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/select\/limit-by#examples-limit-by-all \"Direct link to Examples\")\nSample table:\n```\nCREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;\nINSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);\n```\nQueries:\n```\nSELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id\n```\n```\n┌─id─┬─val─┐\n│  1 │  10 │\n│  1 │  11 │\n│  2 │  20 │\n│  2 │  21 │\n└────┴─────┘\n```\n```\nSELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id\n```\n```\n┌─id─┬─val─┐\n│  1 │  11 │\n│  1 │  12 │\n│  2 │  21 │\n└────┴─────┘\n```\nThe `SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id` query returns the same result.\n\nUsing `LIMIT BY ALL`:\n```\nSELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY ALL\n```\nThis is equivalent to:\n```\nSELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY id, val\n```\nThe following query returns the top 5 referrers for each `domain, device_type` pair with a maximum of 100 rows in total (`LIMIT n BY + LIMIT`).\n```\nSELECT\n    domainWithoutWWW(URL) AS domain,\n    domainWithoutWWW(REFERRER_URL) AS referrer,\n    device_type,\n    count() cnt\nFROM hits\nGROUP BY domain, referrer, device_type\nORDER BY cnt DESC\nLIMIT 5 BY domain, device_type\nLIMIT 100\n```","meta_canonical":null}

3. Robots.txt Check

Query:

Response:

4. Spam/Ban Check

Query:

Response:

5. Seen Status Check

ℹ️ Skipped - page is already crawled

📄

INDEXABLE

✅

CRAWLED

2 days ago

🤖

ROBOTS ALLOWED

Page Info Filters

Filter	Status	Condition	Details
HTTP status	PASS	`download_http_code = 200`	HTTP 200
Age cutoff	PASS	`download_stamp > now() - 6 MONTH`	0.1 months ago
History drop	PASS	`isNull(history_drop_reason)`	No drop reason
Spam/ban	PASS	`fh_dont_index != 1 AND ml_spam_score = 0`	ml_spam_score=0
Canonical	PASS	`meta_canonical IS NULL OR = '' OR = src_unparsed`	Not set

Page Details

Property	Value
URL	https://clickhouse.com/docs/sql-reference/statements/select/limit-by
Last Crawled	2026-04-07 23:08:09 (2 days ago)
First Indexed	2025-02-21 02:31:48 (1 year ago)
HTTP Status Code	200
Meta Title	LIMIT BY Clause \| ClickHouse Docs
Meta Description	Documentation for LIMIT BY Clause
Meta Canonical	null
Boilerpipe Text	A query with the LIMIT n BY expressions clause selects the first n rows for each distinct value of expressions . The key for LIMIT BY can contain any number of expressions . ClickHouse supports the following syntax variants: LIMIT [offset_value, ]n BY expressions LIMIT n OFFSET offset_value BY expressions During query processing, ClickHouse selects data ordered by sorting key. The sorting key is set explicitly using an ORDER BY clause or implicitly as a property of the table engine (row order is only guaranteed when using ORDER BY , otherwise the row blocks will not be ordered due to multi-threading). Then ClickHouse applies LIMIT n BY expressions and returns the first n rows for each distinct combination of expressions . If OFFSET is specified, then for each data block that belongs to a distinct combination of expressions , ClickHouse skips offset_value number of rows from the beginning of the block and returns a maximum of n rows as a result. If offset_value is bigger than the number of rows in the data block, ClickHouse returns zero rows from the block. Note LIMIT BY is not related to LIMIT . They can both be used in the same query. If you want to use column numbers instead of column names in the LIMIT BY clause, enable the setting enable_positional_arguments . Examples Sample table: CREATE TABLE limit_by ( id Int , val Int ) ENGINE = Memory ; INSERT INTO limit_by VALUES ( 1 , 10 ) , ( 1 , 11 ) , ( 1 , 12 ) , ( 2 , 20 ) , ( 2 , 21 ) ; Queries: SELECT * FROM limit_by ORDER BY id , val LIMIT 2 BY id ┌─id─┬─val─┐ │ 1 │ 10 │ │ 1 │ 11 │ │ 2 │ 20 │ │ 2 │ 21 │ └────┴─────┘ SELECT * FROM limit_by ORDER BY id , val LIMIT 1 , 2 BY id ┌─id─┬─val─┐ │ 1 │ 11 │ │ 1 │ 12 │ │ 2 │ 21 │ └────┴─────┘ The SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id query returns the same result. The following query returns the top 5 referrers for each domain, device_type pair with a maximum of 100 rows in total ( LIMIT n BY + LIMIT ). SELECT domainWithoutWWW ( URL ) AS domain , domainWithoutWWW ( REFERRER_URL ) AS referrer , device_type , count ( ) cnt FROM hits GROUP BY domain , referrer , device_type ORDER BY cnt DESC LIMIT 5 BY domain , device_type LIMIT 100 LIMIT BY ALL LIMIT BY ALL is equivalent to listing all the SELECT-ed expressions that are not aggregate functions. For example: SELECT col1 , col2 , col3 FROM table LIMIT 2 BY ALL is the same as SELECT col1 , col2 , col3 FROM table LIMIT 2 BY col1 , col2 , col3 For a special case that if there is a function having both aggregate functions and other fields as its arguments, the LIMIT BY keys will contain the maximum non-aggregate fields we can extract from it. For example: SELECT substring ( a , 4 , 2 ) , substring ( substring ( a , 1 , 2 ) , 1 , count ( b ) ) FROM t LIMIT 2 BY ALL is the same as SELECT substring ( a , 4 , 2 ) , substring ( substring ( a , 1 , 2 ) , 1 , count ( b ) ) FROM t LIMIT 2 BY substring ( a , 4 , 2 ) , substring ( a , 1 , 2 ) Examples Sample table: CREATE TABLE limit_by ( id Int , val Int ) ENGINE = Memory ; INSERT INTO limit_by VALUES ( 1 , 10 ) , ( 1 , 11 ) , ( 1 , 12 ) , ( 2 , 20 ) , ( 2 , 21 ) ; Queries: SELECT * FROM limit_by ORDER BY id , val LIMIT 2 BY id ┌─id─┬─val─┐ │ 1 │ 10 │ │ 1 │ 11 │ │ 2 │ 20 │ │ 2 │ 21 │ └────┴─────┘ SELECT * FROM limit_by ORDER BY id , val LIMIT 1 , 2 BY id ┌─id─┬─val─┐ │ 1 │ 11 │ │ 1 │ 12 │ │ 2 │ 21 │ └────┴─────┘ The SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id query returns the same result. Using LIMIT BY ALL : SELECT id , val FROM limit_by ORDER BY id , val LIMIT 2 BY ALL This is equivalent to: SELECT id , val FROM limit_by ORDER BY id , val LIMIT 2 BY id , val The following query returns the top 5 referrers for each domain, device_type pair with a maximum of 100 rows in total ( LIMIT n BY + LIMIT ). SELECT domainWithoutWWW ( URL ) AS domain , domainWithoutWWW ( REFERRER_URL ) AS referrer , device_type , count ( ) cnt FROM hits GROUP BY domain , referrer , device_type ORDER BY cnt DESC LIMIT 5 BY domain , device_type LIMIT 100
Markdown	[Skip to main content](https://clickhouse.com/docs/sql-reference/statements/select/limit-by#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.7k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=66b0bd70-8178-4af1-8668-a09558d0d04d&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&utm_ga=GA1.1.510657712.1775603292) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=66b0bd70-8178-4af1-8668-a09558d0d04d&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&utm_ga=GA1.1.510657712.1775603292) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [English](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [日本語](https://clickhouse.com/docs/jp/sql-reference/statements/select/limit-by) - [中文](https://clickhouse.com/docs/zh/sql-reference/statements/select/limit-by) - [Русский](https://clickhouse.com/docs/ru/sql-reference/statements/select/limit-by) - [한국어](https://clickhouse.com/docs/ko/sql-reference/statements/select/limit-by) [Skip to main content](https://clickhouse.com/docs/sql-reference/statements/select/limit-by#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.7k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=66b0bd70-8178-4af1-8668-a09558d0d04d&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&utm_ga=GA1.1.510657712.1775603292) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=66b0bd70-8178-4af1-8668-a09558d0d04d&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&utm_ga=GA1.1.510657712.1775603292) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [English](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [日本語](https://clickhouse.com/docs/jp/sql-reference/statements/select/limit-by) - [中文](https://clickhouse.com/docs/zh/sql-reference/statements/select/limit-by) - [Русский](https://clickhouse.com/docs/ru/sql-reference/statements/select/limit-by) - [한국어](https://clickhouse.com/docs/ko/sql-reference/statements/select/limit-by) [Search`Ctrl``K`](https://clickhouse.com/docs/search) - [Introduction](https://clickhouse.com/docs/sql-reference) - [Syntax](https://clickhouse.com/docs/sql-reference/syntax) - [Input and Output Formats](https://clickhouse.com/docs/sql-reference/formats) - [Data types](https://clickhouse.com/docs/sql-reference/data-types) - [Statements](https://clickhouse.com/docs/sql-reference/statements) - [SELECT](https://clickhouse.com/docs/sql-reference/statements/select) - [ALL](https://clickhouse.com/docs/sql-reference/statements/select/all) - [APPLY](https://clickhouse.com/docs/sql-reference/statements/select/apply-modifier) - [ARRAY JOIN](https://clickhouse.com/docs/sql-reference/statements/select/array-join) - [DISTINCT](https://clickhouse.com/docs/sql-reference/statements/select/distinct) - [EXCEPT](https://clickhouse.com/docs/sql-reference/statements/select/except) - [EXCEPT](https://clickhouse.com/docs/sql-reference/statements/select/except-modifier) - [FORMAT](https://clickhouse.com/docs/sql-reference/statements/select/format) - [FROM](https://clickhouse.com/docs/sql-reference/statements/select/from) - [GROUP BY](https://clickhouse.com/docs/sql-reference/statements/select/group-by) - [HAVING](https://clickhouse.com/docs/sql-reference/statements/select/having) - [INTERSECT](https://clickhouse.com/docs/sql-reference/statements/select/intersect) - [INTO OUTFILE](https://clickhouse.com/docs/sql-reference/statements/select/into-outfile) - [JOIN](https://clickhouse.com/docs/sql-reference/statements/select/join) - [LIMIT BY](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [LIMIT](https://clickhouse.com/docs/sql-reference/statements/select/limit) - [OFFSET](https://clickhouse.com/docs/sql-reference/statements/select/offset) - [ORDER BY](https://clickhouse.com/docs/sql-reference/statements/select/order-by) - [PREWHERE](https://clickhouse.com/docs/sql-reference/statements/select/prewhere) - [QUALIFY](https://clickhouse.com/docs/sql-reference/statements/select/qualify) - [REPLACE](https://clickhouse.com/docs/sql-reference/statements/select/replace-modifier) - [SAMPLE](https://clickhouse.com/docs/sql-reference/statements/select/sample) - [UNION](https://clickhouse.com/docs/sql-reference/statements/select/union) - [WHERE](https://clickhouse.com/docs/sql-reference/statements/select/where) - [WITH](https://clickhouse.com/docs/sql-reference/statements/select/with) - [INSERT INTO](https://clickhouse.com/docs/sql-reference/statements/insert-into) - [CREATE](https://clickhouse.com/docs/sql-reference/statements/create) - [ALTER](https://clickhouse.com/docs/sql-reference/statements/alter) - [DELETE](https://clickhouse.com/docs/sql-reference/statements/delete) - [SYSTEM](https://clickhouse.com/docs/sql-reference/statements/system) - [SHOW](https://clickhouse.com/docs/sql-reference/statements/show) - [GRANT](https://clickhouse.com/docs/sql-reference/statements/grant) - [EXPLAIN](https://clickhouse.com/docs/sql-reference/statements/explain) - [REVOKE](https://clickhouse.com/docs/sql-reference/statements/revoke) - [UPDATE](https://clickhouse.com/docs/sql-reference/statements/update) - [ATTACH](https://clickhouse.com/docs/sql-reference/statements/attach) - [CHECK TABLE](https://clickhouse.com/docs/sql-reference/statements/check-table) - [DESCRIBE TABLE](https://clickhouse.com/docs/sql-reference/statements/describe-table) - [DETACH](https://clickhouse.com/docs/sql-reference/statements/detach) - [DROP](https://clickhouse.com/docs/sql-reference/statements/drop) - [EXISTS](https://clickhouse.com/docs/sql-reference/statements/exists) - [KILL](https://clickhouse.com/docs/sql-reference/statements/kill) - [OPTIMIZE](https://clickhouse.com/docs/sql-reference/statements/optimize) - [RENAME](https://clickhouse.com/docs/sql-reference/statements/rename) - [EXCHANGE](https://clickhouse.com/docs/sql-reference/statements/exchange) - [SET](https://clickhouse.com/docs/sql-reference/statements/set) - [SET ROLE](https://clickhouse.com/docs/sql-reference/statements/set-role) - [TRUNCATE](https://clickhouse.com/docs/sql-reference/statements/truncate) - [EXECUTE AS](https://clickhouse.com/docs/sql-reference/statements/execute_as) - [PARALLEL WITH](https://clickhouse.com/docs/sql-reference/statements/parallel_with) - [USE](https://clickhouse.com/docs/sql-reference/statements/use) - [WATCH](https://clickhouse.com/docs/sql-reference/statements/watch) - [MOVE](https://clickhouse.com/docs/sql-reference/statements/move) - [CHECK GRANT](https://clickhouse.com/docs/sql-reference/statements/check-grant) - [UNDROP](https://clickhouse.com/docs/sql-reference/statements/undrop) - [Operators](https://clickhouse.com/docs/sql-reference/operators) - [Engines](https://clickhouse.com/docs/engines) - [Database Engines](https://clickhouse.com/docs/engines/database-engines) - [Table Engines](https://clickhouse.com/docs/engines/table-engines) - [Functions](https://clickhouse.com/docs/sql-reference/functions) - [Regular functions](https://clickhouse.com/docs/sql-reference/functions/regular-functions) - [Aggregate functions](https://clickhouse.com/docs/sql-reference/aggregate-functions) - [Table functions](https://clickhouse.com/docs/sql-reference/table-functions) - [Window functions](https://clickhouse.com/docs/sql-reference/window-functions) - [Formats](https://clickhouse.com/docs/interfaces/formats) - [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes) - [Introduction](https://clickhouse.com/docs/sql-reference) - [Statements](https://clickhouse.com/docs/sql-reference/statements) - [SELECT](https://clickhouse.com/docs/sql-reference/statements/select) - LIMIT BY [Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/limit-by.md) # LIMIT BY Clause A query with the `LIMIT n BY expressions` clause selects the first `n` rows for each distinct value of `expressions`. The key for `LIMIT BY` can contain any number of [expressions](https://clickhouse.com/docs/sql-reference/syntax#expressions). ClickHouse supports the following syntax variants: - `LIMIT [offset_value, ]n BY expressions` - `LIMIT n OFFSET offset_value BY expressions` During query processing, ClickHouse selects data ordered by sorting key. The sorting key is set explicitly using an [ORDER BY](https://clickhouse.com/docs/sql-reference/statements/select/order-by) clause or implicitly as a property of the table engine (row order is only guaranteed when using [ORDER BY](https://clickhouse.com/docs/sql-reference/statements/select/order-by), otherwise the row blocks will not be ordered due to multi-threading). Then ClickHouse applies `LIMIT n BY expressions` and returns the first `n` rows for each distinct combination of `expressions`. If `OFFSET` is specified, then for each data block that belongs to a distinct combination of `expressions`, ClickHouse skips `offset_value` number of rows from the beginning of the block and returns a maximum of `n` rows as a result. If `offset_value` is bigger than the number of rows in the data block, ClickHouse returns zero rows from the block. Note `LIMIT BY` is not related to [LIMIT](https://clickhouse.com/docs/sql-reference/statements/select/limit). They can both be used in the same query. If you want to use column numbers instead of column names in the `LIMIT BY` clause, enable the setting [enable\_positional\_arguments](https://clickhouse.com/docs/operations/settings/settings#enable_positional_arguments). ## Examples[](https://clickhouse.com/docs/sql-reference/statements/select/limit-by#examples "Direct link to Examples") Sample table: ``` CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory; INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21); ``` Queries: ``` SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id ``` ``` ┌─id─┬─val─┐ │ 1 │ 10 │ │ 1 │ 11 │ │ 2 │ 20 │ │ 2 │ 21 │ └────┴─────┘ ``` ``` SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id ``` ``` ┌─id─┬─val─┐ │ 1 │ 11 │ │ 1 │ 12 │ │ 2 │ 21 │ └────┴─────┘ ``` The `SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id` query returns the same result. The following query returns the top 5 referrers for each `domain, device_type` pair with a maximum of 100 rows in total (`LIMIT n BY + LIMIT`). ``` SELECT domainWithoutWWW(URL) AS domain, domainWithoutWWW(REFERRER_URL) AS referrer, device_type, count() cnt FROM hits GROUP BY domain, referrer, device_type ORDER BY cnt DESC LIMIT 5 BY domain, device_type LIMIT 100 ``` ## LIMIT BY ALL[](https://clickhouse.com/docs/sql-reference/statements/select/limit-by#limit-by-all "Direct link to LIMIT BY ALL") `LIMIT BY ALL` is equivalent to listing all the SELECT-ed expressions that are not aggregate functions. For example: ``` SELECT col1, col2, col3 FROM table LIMIT 2 BY ALL ``` is the same as ``` SELECT col1, col2, col3 FROM table LIMIT 2 BY col1, col2, col3 ``` For a special case that if there is a function having both aggregate functions and other fields as its arguments, the `LIMIT BY` keys will contain the maximum non-aggregate fields we can extract from it. For example: ``` SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY ALL ``` is the same as ``` SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY substring(a, 4, 2), substring(a, 1, 2) ``` ## Examples[](https://clickhouse.com/docs/sql-reference/statements/select/limit-by#examples-limit-by-all "Direct link to Examples") Sample table: ``` CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory; INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21); ``` Queries: ``` SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id ``` ``` ┌─id─┬─val─┐ │ 1 │ 10 │ │ 1 │ 11 │ │ 2 │ 20 │ │ 2 │ 21 │ └────┴─────┘ ``` ``` SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id ``` ``` ┌─id─┬─val─┐ │ 1 │ 11 │ │ 1 │ 12 │ │ 2 │ 21 │ └────┴─────┘ ``` The `SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id` query returns the same result. Using `LIMIT BY ALL`: ``` SELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY ALL ``` This is equivalent to: ``` SELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY id, val ``` The following query returns the top 5 referrers for each `domain, device_type` pair with a maximum of 100 rows in total (`LIMIT n BY + LIMIT`). ``` SELECT domainWithoutWWW(URL) AS domain, domainWithoutWWW(REFERRER_URL) AS referrer, device_type, count() cnt FROM hits GROUP BY domain, referrer, device_type ORDER BY cnt DESC LIMIT 5 BY domain, device_type LIMIT 100 ``` [Previous JOIN](https://clickhouse.com/docs/sql-reference/statements/select/join) [Next LIMIT](https://clickhouse.com/docs/sql-reference/statements/select/limit) - [Examples](https://clickhouse.com/docs/sql-reference/statements/select/limit-by#examples) - [LIMIT BY ALL](https://clickhouse.com/docs/sql-reference/statements/select/limit-by#limit-by-all) - [Examples](https://clickhouse.com/docs/sql-reference/statements/select/limit-by#examples-limit-by-all) Was this page helpful? ###### Try ClickHouse Cloud for FREE Separation of storage and compute, automatic scaling, built-in SQL console, and lots more. \$300 in free credits when signing up. [Try it for Free](https://console.clickhouse.cloud/signUp?loc=doc-card-banner&glxid=66b0bd70-8178-4af1-8668-a09558d0d04d&pagePath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&origPath=%2Fdocs%2Fsql-reference%2Fstatements%2Fselect%2Flimit-by&utm_ga=GA1.1.510657712.1775603292) © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91) © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) EN - Get started▼ - Cloud▼ - Manage data▼ - Server admin▼ - Reference▼ - Integrations▼ - ClickStack▼ - chDB▼ - About▼ [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) EN main-menu - Introduction▼ - [Syntax](https://clickhouse.com/docs/sql-reference/syntax) - [Input and Output Formats](https://clickhouse.com/docs/sql-reference/formats) - Data types▼ - Statements▼ - SELECT▼ - [ALL](https://clickhouse.com/docs/sql-reference/statements/select/all) - [APPLY](https://clickhouse.com/docs/sql-reference/statements/select/apply-modifier) - [ARRAY JOIN](https://clickhouse.com/docs/sql-reference/statements/select/array-join) - [DISTINCT](https://clickhouse.com/docs/sql-reference/statements/select/distinct) - [EXCEPT](https://clickhouse.com/docs/sql-reference/statements/select/except) - [EXCEPT](https://clickhouse.com/docs/sql-reference/statements/select/except-modifier) - [FORMAT](https://clickhouse.com/docs/sql-reference/statements/select/format) - [FROM](https://clickhouse.com/docs/sql-reference/statements/select/from) - [GROUP BY](https://clickhouse.com/docs/sql-reference/statements/select/group-by) - [HAVING](https://clickhouse.com/docs/sql-reference/statements/select/having) - [INTERSECT](https://clickhouse.com/docs/sql-reference/statements/select/intersect) - [INTO OUTFILE](https://clickhouse.com/docs/sql-reference/statements/select/into-outfile) - [JOIN](https://clickhouse.com/docs/sql-reference/statements/select/join) - [LIMIT BY](https://clickhouse.com/docs/sql-reference/statements/select/limit-by) - [LIMIT](https://clickhouse.com/docs/sql-reference/statements/select/limit) - [OFFSET](https://clickhouse.com/docs/sql-reference/statements/select/offset) - [ORDER BY](https://clickhouse.com/docs/sql-reference/statements/select/order-by) - [PREWHERE](https://clickhouse.com/docs/sql-reference/statements/select/prewhere) - [QUALIFY](https://clickhouse.com/docs/sql-reference/statements/select/qualify) - [REPLACE](https://clickhouse.com/docs/sql-reference/statements/select/replace-modifier) - [SAMPLE](https://clickhouse.com/docs/sql-reference/statements/select/sample) - [UNION](https://clickhouse.com/docs/sql-reference/statements/select/union) - [WHERE](https://clickhouse.com/docs/sql-reference/statements/select/where) - [WITH](https://clickhouse.com/docs/sql-reference/statements/select/with) - [INSERT INTO](https://clickhouse.com/docs/sql-reference/statements/insert-into) - CREATE▼ - ALTER▼ - [DELETE](https://clickhouse.com/docs/sql-reference/statements/delete) - [SYSTEM](https://clickhouse.com/docs/sql-reference/statements/system) - [SHOW](https://clickhouse.com/docs/sql-reference/statements/show) - [GRANT](https://clickhouse.com/docs/sql-reference/statements/grant) - [EXPLAIN](https://clickhouse.com/docs/sql-reference/statements/explain) - [REVOKE](https://clickhouse.com/docs/sql-reference/statements/revoke) - [UPDATE](https://clickhouse.com/docs/sql-reference/statements/update) - [ATTACH](https://clickhouse.com/docs/sql-reference/statements/attach) - [CHECK TABLE](https://clickhouse.com/docs/sql-reference/statements/check-table) - [DESCRIBE TABLE](https://clickhouse.com/docs/sql-reference/statements/describe-table) - [DETACH](https://clickhouse.com/docs/sql-reference/statements/detach) - [DROP](https://clickhouse.com/docs/sql-reference/statements/drop) - [EXISTS](https://clickhouse.com/docs/sql-reference/statements/exists) - [KILL](https://clickhouse.com/docs/sql-reference/statements/kill) - [OPTIMIZE](https://clickhouse.com/docs/sql-reference/statements/optimize) - [RENAME](https://clickhouse.com/docs/sql-reference/statements/rename) - [EXCHANGE](https://clickhouse.com/docs/sql-reference/statements/exchange) - [SET](https://clickhouse.com/docs/sql-reference/statements/set) - [SET ROLE](https://clickhouse.com/docs/sql-reference/statements/set-role) - [TRUNCATE](https://clickhouse.com/docs/sql-reference/statements/truncate) - [EXECUTE AS](https://clickhouse.com/docs/sql-reference/statements/execute_as) - [PARALLEL WITH](https://clickhouse.com/docs/sql-reference/statements/parallel_with) - [USE](https://clickhouse.com/docs/sql-reference/statements/use) - [WATCH](https://clickhouse.com/docs/sql-reference/statements/watch) - [MOVE](https://clickhouse.com/docs/sql-reference/statements/move) - [CHECK GRANT](https://clickhouse.com/docs/sql-reference/statements/check-grant) - [UNDROP](https://clickhouse.com/docs/sql-reference/statements/undrop) - Operators▼ - Engines▼ - Functions▼ - Formats▼ - [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes)
Readable Markdown	A query with the `LIMIT n BY expressions` clause selects the first `n` rows for each distinct value of `expressions`. The key for `LIMIT BY` can contain any number of [expressions](https://clickhouse.com/docs/sql-reference/syntax#expressions). ClickHouse supports the following syntax variants: - `LIMIT [offset_value, ]n BY expressions` - `LIMIT n OFFSET offset_value BY expressions` During query processing, ClickHouse selects data ordered by sorting key. The sorting key is set explicitly using an [ORDER BY](https://clickhouse.com/docs/sql-reference/statements/select/order-by) clause or implicitly as a property of the table engine (row order is only guaranteed when using [ORDER BY](https://clickhouse.com/docs/sql-reference/statements/select/order-by), otherwise the row blocks will not be ordered due to multi-threading). Then ClickHouse applies `LIMIT n BY expressions` and returns the first `n` rows for each distinct combination of `expressions`. If `OFFSET` is specified, then for each data block that belongs to a distinct combination of `expressions`, ClickHouse skips `offset_value` number of rows from the beginning of the block and returns a maximum of `n` rows as a result. If `offset_value` is bigger than the number of rows in the data block, ClickHouse returns zero rows from the block. Note `LIMIT BY` is not related to [LIMIT](https://clickhouse.com/docs/sql-reference/statements/select/limit). They can both be used in the same query. If you want to use column numbers instead of column names in the `LIMIT BY` clause, enable the setting [enable\_positional\_arguments](https://clickhouse.com/docs/operations/settings/settings#enable_positional_arguments). ## Examples[](https://clickhouse.com/docs/sql-reference/statements/select/limit-by#examples "Direct link to Examples") Sample table: ``` CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory; INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21); ``` Queries: ``` SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id ``` ``` ┌─id─┬─val─┐ │ 1 │ 10 │ │ 1 │ 11 │ │ 2 │ 20 │ │ 2 │ 21 │ └────┴─────┘ ``` ``` SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id ``` ``` ┌─id─┬─val─┐ │ 1 │ 11 │ │ 1 │ 12 │ │ 2 │ 21 │ └────┴─────┘ ``` The `SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id` query returns the same result. The following query returns the top 5 referrers for each `domain, device_type` pair with a maximum of 100 rows in total (`LIMIT n BY + LIMIT`). ``` SELECT domainWithoutWWW(URL) AS domain, domainWithoutWWW(REFERRER_URL) AS referrer, device_type, count() cnt FROM hits GROUP BY domain, referrer, device_type ORDER BY cnt DESC LIMIT 5 BY domain, device_type LIMIT 100 ``` ## LIMIT BY ALL[](https://clickhouse.com/docs/sql-reference/statements/select/limit-by#limit-by-all "Direct link to LIMIT BY ALL") `LIMIT BY ALL` is equivalent to listing all the SELECT-ed expressions that are not aggregate functions. For example: ``` SELECT col1, col2, col3 FROM table LIMIT 2 BY ALL ``` is the same as ``` SELECT col1, col2, col3 FROM table LIMIT 2 BY col1, col2, col3 ``` For a special case that if there is a function having both aggregate functions and other fields as its arguments, the `LIMIT BY` keys will contain the maximum non-aggregate fields we can extract from it. For example: ``` SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY ALL ``` is the same as ``` SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY substring(a, 4, 2), substring(a, 1, 2) ``` ## Examples[](https://clickhouse.com/docs/sql-reference/statements/select/limit-by#examples-limit-by-all "Direct link to Examples") Sample table: ``` CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory; INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21); ``` Queries: ``` SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id ``` ``` ┌─id─┬─val─┐ │ 1 │ 10 │ │ 1 │ 11 │ │ 2 │ 20 │ │ 2 │ 21 │ └────┴─────┘ ``` ``` SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id ``` ``` ┌─id─┬─val─┐ │ 1 │ 11 │ │ 1 │ 12 │ │ 2 │ 21 │ └────┴─────┘ ``` The `SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id` query returns the same result. Using `LIMIT BY ALL`: ``` SELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY ALL ``` This is equivalent to: ``` SELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY id, val ``` The following query returns the top 5 referrers for each `domain, device_type` pair with a maximum of 100 rows in total (`LIMIT n BY + LIMIT`). ``` SELECT domainWithoutWWW(URL) AS domain, domainWithoutWWW(REFERRER_URL) AS referrer, device_type, count() cnt FROM hits GROUP BY domain, referrer, device_type ORDER BY cnt DESC LIMIT 5 BY domain, device_type LIMIT 100 ```
Shard	89 (laksa)
Root Hash	12633450985039531489
Unparsed URL	com,clickhouse!/docs/sql-reference/statements/select/limit-by s443