{"id":12241,"date":"2024-03-14T15:26:25","date_gmt":"2024-03-14T15:26:25","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/"},"modified":"2025-08-04T23:10:54","modified_gmt":"2025-08-04T23:10:54","slug":"basic-usage-of-rownum","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/","title":{"rendered":"Oracle ROWNUM: Basic Usage Guide"},"content":{"rendered":"

ROWNUM is a pseudocolumn that can be used to return the row numbers in a result set. In Oracle, ROWNUM is assigned after the data is retrieved, therefore it cannot be directly used in the WHERE clause. Here is the basic usage of ROWNUM:<\/p>\n

    \n
  1. Retrieve the first N rows of data:
    \nSELECT * FROM table_name WHERE ROWNUM <= N;<\/li>\n
  2. Pagination query:
    \nSelect all columns from the table “table_name” ordered by “column_name”, limiting the result set to a specific number of rows equal to the page size starting from a particular row number.<\/li>\n
  3. This query first sorts the result set, and then uses ROWNUM for paging. “start” is the index of the starting row, and “page_size” is the number of rows displayed per page.<\/li>\n
  4. Delete duplicate data using ROWNUM and exclude the WHERE clause:
    \nDELETE FROM table_name WHERE rowid NOT IN (SELECT MIN(rowid) FROM table_name GROUP BY column_name);<\/li>\n
  5. This query uses ROWNUM to remove duplicate data in a table. The inner SELECT statement first finds the minimum rowid for each duplicate data group using the GROUP BY clause and MIN function. Then, the outer DELETE statement uses ROWNUM to exclude these minimum rowids, effectively deleting the duplicate data.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"

    ROWNUM is a pseudocolumn that can be used to return the row numbers in a result set. In Oracle, ROWNUM is assigned after the data is retrieved, therefore it cannot be directly used in the WHERE clause. Here is the basic usage of ROWNUM: Retrieve the first N rows of data: SELECT * FROM table_name […]<\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[97,472,1116,3276,15983],"class_list":["post-12241","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-database-query","tag-oracle-sql","tag-oracle-tips","tag-rownum","tag-sql-pagination"],"yoast_head":"\nOracle ROWNUM: Basic Usage Guide - Blog - Silicon Cloud<\/title>\n<meta name=\"description\" content=\"Learn Oracle ROWNUM for limiting query results and pagination. Understand pseudocolum behavior and best practices.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle ROWNUM: Basic Usage Guide\" \/>\n<meta property=\"og:description\" content=\"Learn Oracle ROWNUM for limiting query results and pagination. Understand pseudocolum behavior and best practices.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-14T15:26:25+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-04T23:10:54+00:00\" \/>\n<meta name=\"author\" content=\"Isabella Edwards\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Isabella Edwards\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/\"},\"author\":{\"name\":\"Isabella Edwards\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd\"},\"headline\":\"Oracle ROWNUM: Basic Usage Guide\",\"datePublished\":\"2024-03-14T15:26:25+00:00\",\"dateModified\":\"2025-08-04T23:10:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/\"},\"wordCount\":62,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"keywords\":[\"database query\",\"Oracle SQL\",\"Oracle tips\",\"ROWNUM\",\"SQL Pagination\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/\",\"name\":\"Oracle ROWNUM: Basic Usage Guide - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-14T15:26:25+00:00\",\"dateModified\":\"2025-08-04T23:10:54+00:00\",\"description\":\"Learn Oracle ROWNUM for limiting query results and pagination. Understand pseudocolum behavior and best practices.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle ROWNUM: Basic Usage Guide\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd\",\"name\":\"Isabella Edwards\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g\",\"caption\":\"Isabella Edwards\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/isabellaedwards\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle ROWNUM: Basic Usage Guide - Blog - Silicon Cloud","description":"Learn Oracle ROWNUM for limiting query results and pagination. Understand pseudocolum behavior and best practices.","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:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/","og_locale":"en_US","og_type":"article","og_title":"Oracle ROWNUM: Basic Usage Guide","og_description":"Learn Oracle ROWNUM for limiting query results and pagination. Understand pseudocolum behavior and best practices.","og_url":"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-14T15:26:25+00:00","article_modified_time":"2025-08-04T23:10:54+00:00","author":"Isabella Edwards","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Isabella Edwards","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/"},"author":{"name":"Isabella Edwards","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd"},"headline":"Oracle ROWNUM: Basic Usage Guide","datePublished":"2024-03-14T15:26:25+00:00","dateModified":"2025-08-04T23:10:54+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/"},"wordCount":62,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"keywords":["database query","Oracle SQL","Oracle tips","ROWNUM","SQL Pagination"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/","url":"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/","name":"Oracle ROWNUM: Basic Usage Guide - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-14T15:26:25+00:00","dateModified":"2025-08-04T23:10:54+00:00","description":"Learn Oracle ROWNUM for limiting query results and pagination. Understand pseudocolum behavior and best practices.","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/basic-usage-of-rownum\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle ROWNUM: Basic Usage Guide"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd","name":"Isabella Edwards","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g","caption":"Isabella Edwards"},"url":"https:\/\/www.silicloud.com\/blog\/author\/isabellaedwards\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/12241","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=12241"}],"version-history":[{"count":2,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/12241\/revisions"}],"predecessor-version":[{"id":156040,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/12241\/revisions\/156040"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=12241"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=12241"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=12241"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}