{"id":15519,"date":"2024-03-15T11:19:49","date_gmt":"2024-03-15T11:19:49","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/"},"modified":"2025-08-06T19:18:09","modified_gmt":"2025-08-06T19:18:09","slug":"how-can-rownum-be-deduplicated-in-a-mysql-query","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/","title":{"rendered":"How can rownum be deduplicated in a MySQL query?"},"content":{"rendered":"<p>In MySQL, there is no built-in function like ROWNUM in Oracle database to achieve row numbering. However, row numbering can be simulated using variables and duplicates can be removed as needed.<\/p>\n<p>Here is a common method to use variables in query results to assign row numbers and remove duplicates based on those row numbers.<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">SELECT<\/span> t.<span class=\"hljs-operator\">*<\/span> \r\n<span class=\"hljs-keyword\">FROM<\/span> (\r\n    <span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-operator\">*<\/span>,\r\n           <span class=\"hljs-variable\">@rownum<\/span> :<span class=\"hljs-operator\">=<\/span> IF(<span class=\"hljs-variable\">@prev_value<\/span> <span class=\"hljs-operator\">=<\/span> column_to_check, <span class=\"hljs-variable\">@rownum<\/span>, <span class=\"hljs-variable\">@rownum<\/span> <span class=\"hljs-operator\">+<\/span> <span class=\"hljs-number\">1<\/span>) <span class=\"hljs-keyword\">AS<\/span> row_number,\r\n           <span class=\"hljs-variable\">@prev_value<\/span> :<span class=\"hljs-operator\">=<\/span> column_to_check\r\n    <span class=\"hljs-keyword\">FROM<\/span> your_table\r\n    <span class=\"hljs-keyword\">CROSS<\/span> <span class=\"hljs-keyword\">JOIN<\/span> (<span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-variable\">@rownum<\/span> :<span class=\"hljs-operator\">=<\/span> <span class=\"hljs-number\">0<\/span>, <span class=\"hljs-variable\">@prev_value<\/span> :<span class=\"hljs-operator\">=<\/span> <span class=\"hljs-keyword\">NULL<\/span>) <span class=\"hljs-keyword\">AS<\/span> vars\r\n    <span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> column_to_check\r\n) <span class=\"hljs-keyword\">AS<\/span> t\r\n<span class=\"hljs-keyword\">WHERE<\/span> row_number <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-number\">1<\/span>;\r\n<\/code><\/pre>\n<p>In the above example, replace &#8220;column_to_check&#8221; with the column you want to deduplicate based on.<\/p>\n<p>This query uses an internal variable (@rownum) to calculate row numbers and another variable (@prev_value) to track the column value of the previous row. For each row, if the column value of the current row is the same as the column value of the previous row, the row number remains unchanged; otherwise, the row number is incremented by 1. Then, the outer query filters out the non-repetitive rows based on the row number.<\/p>\n<p>Please note that the above method is only applicable for removing duplicates on a single column in the query result. If you need to remove duplicates on multiple columns, you will need to make appropriate modifications.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In MySQL, there is no built-in function like ROWNUM in Oracle database to achieve row numbering. However, row numbering can be simulated using variables and duplicates can be removed as needed. Here is a common method to use variables in query results to assign row numbers and remove duplicates based on those row numbers. SELECT [&hellip;]<\/p>\n","protected":false},"author":12,"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":[453,1402,299,1404,1403],"class_list":["post-15519","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-development","tag-guide","tag-programming","tag-technology","tag-tutorial"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How can rownum be deduplicated in a MySQL query? - Blog - Silicon Cloud<\/title>\n<meta name=\"description\" content=\"Learn about how can rownum be deduplicated in a mysql query?. Comprehensive guide with examples 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\/how-can-rownum-be-deduplicated-in-a-mysql-query\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How can rownum be deduplicated in a MySQL query?\" \/>\n<meta property=\"og:description\" content=\"Learn about how can rownum be deduplicated in a mysql query?. Comprehensive guide with examples and best practices.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/\" \/>\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-15T11:19:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-06T19:18:09+00:00\" \/>\n<meta name=\"author\" content=\"Liam\" \/>\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=\"Liam\" \/>\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\/how-can-rownum-be-deduplicated-in-a-mysql-query\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/\"},\"author\":{\"name\":\"Liam\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/23786905eb7b377f45ddb01c17da7671\"},\"headline\":\"How can rownum be deduplicated in a MySQL query?\",\"datePublished\":\"2024-03-15T11:19:49+00:00\",\"dateModified\":\"2025-08-06T19:18:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/\"},\"wordCount\":189,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"keywords\":[\"Development\",\"guide\",\"programming\",\"technology\",\"tutorial\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/\",\"name\":\"How can rownum be deduplicated in a MySQL query? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T11:19:49+00:00\",\"dateModified\":\"2025-08-06T19:18:09+00:00\",\"description\":\"Learn about how can rownum be deduplicated in a mysql query?. Comprehensive guide with examples and best practices.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How can rownum be deduplicated in a MySQL query?\"}]},{\"@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\/23786905eb7b377f45ddb01c17da7671\",\"name\":\"Liam\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/8d37ed3e7f770dde8bf069ba0b4298688028c3abaacf1131742fc1352d174ebd?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/8d37ed3e7f770dde8bf069ba0b4298688028c3abaacf1131742fc1352d174ebd?s=96&d=mm&r=g\",\"caption\":\"Liam\"},\"sameAs\":[\"http:\/\/Wilson\"],\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/liamwilson\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How can rownum be deduplicated in a MySQL query? - Blog - Silicon Cloud","description":"Learn about how can rownum be deduplicated in a mysql query?. Comprehensive guide with examples 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\/how-can-rownum-be-deduplicated-in-a-mysql-query\/","og_locale":"en_US","og_type":"article","og_title":"How can rownum be deduplicated in a MySQL query?","og_description":"Learn about how can rownum be deduplicated in a mysql query?. Comprehensive guide with examples and best practices.","og_url":"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T11:19:49+00:00","article_modified_time":"2025-08-06T19:18:09+00:00","author":"Liam","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Liam","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/"},"author":{"name":"Liam","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/23786905eb7b377f45ddb01c17da7671"},"headline":"How can rownum be deduplicated in a MySQL query?","datePublished":"2024-03-15T11:19:49+00:00","dateModified":"2025-08-06T19:18:09+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/"},"wordCount":189,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"keywords":["Development","guide","programming","technology","tutorial"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/","url":"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/","name":"How can rownum be deduplicated in a MySQL query? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T11:19:49+00:00","dateModified":"2025-08-06T19:18:09+00:00","description":"Learn about how can rownum be deduplicated in a mysql query?. Comprehensive guide with examples and best practices.","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-can-rownum-be-deduplicated-in-a-mysql-query\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How can rownum be deduplicated in a MySQL query?"}]},{"@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\/23786905eb7b377f45ddb01c17da7671","name":"Liam","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/8d37ed3e7f770dde8bf069ba0b4298688028c3abaacf1131742fc1352d174ebd?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/8d37ed3e7f770dde8bf069ba0b4298688028c3abaacf1131742fc1352d174ebd?s=96&d=mm&r=g","caption":"Liam"},"sameAs":["http:\/\/Wilson"],"url":"https:\/\/www.silicloud.com\/blog\/author\/liamwilson\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/15519","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\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=15519"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/15519\/revisions"}],"predecessor-version":[{"id":48992,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/15519\/revisions\/48992"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=15519"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=15519"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=15519"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}