{"id":4068,"date":"2024-09-19T11:49:05","date_gmt":"2024-09-19T03:49:05","guid":{"rendered":"https:\/\/five.co\/?p=4068"},"modified":"2026-05-04T15:57:47","modified_gmt":"2026-05-04T07:57:47","slug":"rank-over-partition-by-in-sql","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/rank-over-partition-by-in-sql\/","title":{"rendered":"RANK () OVER (PARTITION BY) in SQL: A Deep Dive"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Breakdown of RANK () OVER (PARTITION BY)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">SQL&#8217;s window functions are great for data analysis, and <code>RANK() OVER (PARTITION BY)<\/code> stands out as a particularly useful tool. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This article will explore how this function works, and its practical applications.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-jh5s6 wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-jh5s6 \"><div class=\"eb-toc-container eb-toc-jh5s6  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;Breakdown of RANK () OVER (PARTITION BY)&quot;,&quot;text&quot;:&quot;Breakdown of RANK () OVER (PARTITION BY)&quot;,&quot;link&quot;:&quot;breakdown-of-rank-over-partition-by&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;What is RANK() OVER (PARTITION BY)?&quot;,&quot;text&quot;:&quot;What is RANK() OVER (PARTITION BY)?&quot;,&quot;link&quot;:&quot;what-is-rank-over-partition-by&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;How RANK() OVER (PARTITION BY) Works&quot;,&quot;text&quot;:&quot;How RANK() OVER (PARTITION BY) Works&quot;,&quot;link&quot;:&quot;how-rank-over-partition-by-works&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;RANK() vs. DENSE_RANK() vs. ROW_NUMBER()&quot;,&quot;text&quot;:&quot;RANK() vs. DENSE_RANK() vs. ROW_NUMBER()&quot;,&quot;link&quot;:&quot;rank-vs-dense_rank-vs-row_number&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Real-World Applications&quot;,&quot;text&quot;:&quot;Real-World Applications&quot;,&quot;link&quot;:&quot;real-world-applications&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Identifying Top Performers&quot;,&quot;text&quot;:&quot;Identifying Top Performers&quot;,&quot;link&quot;:&quot;identifying-top-performers&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Percentile Calculations&quot;,&quot;text&quot;:&quot;Percentile Calculations&quot;,&quot;link&quot;:&quot;percentile-calculations&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Time Series Analysis&quot;,&quot;text&quot;:&quot;Time Series Analysis&quot;,&quot;link&quot;:&quot;time-series-analysis&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Performance Considerations&quot;,&quot;text&quot;:&quot;Performance Considerations&quot;,&quot;link&quot;:&quot;performance-considerations&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Common Pitfalls and How to Avoid Them&quot;,&quot;text&quot;:&quot;Common Pitfalls and How to Avoid Them&quot;,&quot;link&quot;:&quot;common-pitfalls-and-how-to-avoid-them&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;FAQ&quot;,&quot;text&quot;:&quot;FAQ&quot;,&quot;link&quot;:&quot;faq&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;What does RANK() OVER (PARTITION BY) do in SQL?&quot;,&quot;text&quot;:&quot;What does RANK() OVER (PARTITION BY) do in SQL?&quot;,&quot;link&quot;:&quot;what-does-rank-over-partition-by-do-in-sql&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;How is RANK() different from ROW_NUMBER() and DENSE_RANK()?&quot;,&quot;text&quot;:&quot;How is RANK() different from ROW_NUMBER() and DENSE_RANK()?&quot;,&quot;link&quot;:&quot;how-is-rank-different-from-row_number-and-dense_rank&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;When should you use PARTITION BY with RANK()?&quot;,&quot;text&quot;:&quot;When should you use PARTITION BY with RANK()?&quot;,&quot;link&quot;:&quot;when-should-you-use-partition-by-with-rank&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;What happens if there are ties when using RANK()?&quot;,&quot;text&quot;:&quot;What happens if there are ties when using RANK()?&quot;,&quot;link&quot;:&quot;what-happens-if-there-are-ties-when-using-rank&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;What are common use cases for RANK() OVER (PARTITION BY)?&quot;,&quot;text&quot;:&quot;What are common use cases for RANK() OVER (PARTITION BY)?&quot;,&quot;link&quot;:&quot;what-are-common-use-cases-for-rank-over-partition-by&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Conclusion&quot;,&quot;text&quot;:&quot;Conclusion&quot;,&quot;link&quot;:&quot;conclusion&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Breakdown of RANK () OVER (PARTITION BY)&quot;,&quot;value&quot;:&quot;breakdown-of-rank-over-partition-by&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;What is RANK() OVER (PARTITION BY)?&quot;,&quot;value&quot;:&quot;what-is-rank-over-partition-by&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;How RANK() OVER (PARTITION BY) Works&quot;,&quot;value&quot;:&quot;how-rank-over-partition-by-works&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;RANK() vs. DENSE_RANK() vs. ROW_NUMBER()&quot;,&quot;value&quot;:&quot;rank-vs-dense_rank-vs-row_number&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Real-World Applications&quot;,&quot;value&quot;:&quot;real-world-applications&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Identifying Top Performers&quot;,&quot;value&quot;:&quot;identifying-top-performers&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Percentile Calculations&quot;,&quot;value&quot;:&quot;percentile-calculations&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Time Series Analysis&quot;,&quot;value&quot;:&quot;time-series-analysis&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Performance Considerations&quot;,&quot;value&quot;:&quot;performance-considerations&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Common Pitfalls and How to Avoid Them&quot;,&quot;value&quot;:&quot;common-pitfalls-and-how-to-avoid-them&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;FAQ&quot;,&quot;value&quot;:&quot;faq&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;What does RANK() OVER (PARTITION BY) do in SQL?&quot;,&quot;value&quot;:&quot;what-does-rank-over-partition-by-do-in-sql&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;How is RANK() different from ROW_NUMBER() and DENSE_RANK()?&quot;,&quot;value&quot;:&quot;how-is-rank-different-from-row_number-and-dense_rank&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;When should you use PARTITION BY with RANK()?&quot;,&quot;value&quot;:&quot;when-should-you-use-partition-by-with-rank&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;What happens if there are ties when using RANK()?&quot;,&quot;value&quot;:&quot;what-happens-if-there-are-ties-when-using-rank&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;What are common use cases for RANK() OVER (PARTITION BY)?&quot;,&quot;value&quot;:&quot;what-are-common-use-cases-for-rank-over-partition-by&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Conclusion&quot;,&quot;value&quot;:&quot;conclusion&quot;,&quot;isDelete&quot;:false}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#what-is-rank-over-partition-by\">What is RANK() OVER (PARTITION BY)?<\/a><li><a href=\"#how-rank-over-partition-by-works\">How RANK() OVER (PARTITION BY) Works<\/a><li><a href=\"#rank-vs-dense_rank-vs-row_number\">RANK() vs. DENSE_RANK() vs. ROW_NUMBER()<\/a><li><a href=\"#real-world-applications\">Real-World Applications<\/a><li><a href=\"#performance-considerations\">Performance Considerations<\/a><li><a href=\"#common-pitfalls-and-how-to-avoid-them\">Common Pitfalls and How to Avoid Them<\/a><li><a href=\"#faq\">FAQ<\/a><ul class=\"eb-toc__list\"><li><a href=\"#what-does-rank-over-partition-by-do-in-sql\">What does RANK() OVER (PARTITION BY) do in SQL?<\/a><li><a href=\"#how-is-rank-different-from-row_number-and-dense_rank\">How is RANK() different from ROW_NUMBER() and DENSE_RANK()?<\/a><li><a href=\"#when-should-you-use-partition-by-with-rank\">When should you use PARTITION BY with RANK()?<\/a><li><a href=\"#what-happens-if-there-are-ties-when-using-rank\">What happens if there are ties when using RANK()?<\/a><li><a href=\"#what-are-common-use-cases-for-rank-over-partition-by\">What are common use cases for RANK() OVER (PARTITION BY)?<\/a><\/li><\/ul><li><a href=\"#conclusion\">Conclusion<\/a><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(25px + 0.390625vw);\"><strong>SQL Made Simple - Try Five<\/strong><br \/><span style=\"font-size: 14pt;\">Five is the Easiest Way to Create, Model and Query a SQL Database<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\" data-wplink-edit=\"true\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Started<\/strong><\/button><\/a><br \/><span style=\"color: #ffffff; font-size: revert;\">___<\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"text-decoration: underline;\"><strong><span style=\"color: #ffffff; text-decoration: underline;\">Here&#8217;s What You Can Do With Five<\/span><\/strong><\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"color: #ffffff;\">Create and Model a SQL Database \u2705<br \/>Write or Build SQL Queries \u2705<br \/>Visualize Queries as Charts or in Dashboards \u2705<br \/>Add CRUD Permissions to Control Data Access \u2705<br \/>Host Your Database Online \u2705\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"color: #f1ebda; background-color: #4588d8;\"><em><strong>&#8220;Five bridges the gap between SQL and the web, <br \/>allowing me to create full-stack applications almost entirely in SQL&#8221;<\/strong><\/em><\/span><br \/><span style=\"color: #ffffff;\">&#8211; Crag Jones, Database Administrator (DBA)<\/span><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">What is RANK() OVER (PARTITION BY)?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">At its core, <code>RANK() OVER (PARTITION BY)<\/code> is a window function that assigns a rank to each row within a partition of a result set. It&#8217;s like creating a mini-leaderboard for each group in your data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s break it down:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>RANK()<\/code>: This function assigns a rank to each row based on the specified ordering.<\/li>\n\n\n\n<li><code>OVER<\/code>: This keyword defines the window of rows the function operates on.<\/li>\n\n\n\n<li><code>PARTITION BY<\/code>: This clause divides the result set into partitions to which the function is applied separately.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">How RANK() OVER (PARTITION BY) Works<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To understand how this function works, let&#8217;s dive into a practical example. Imagine we have a table of sales data:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>CREATE TABLE sales (\n    employee_id INT,\n    department VARCHAR(50),\n    sale_amount DECIMAL(10, 2)\n);\n\nINSERT INTO sales VALUES\n(1, 'Electronics', 1000.00),\n(1, 'Electronics', 1500.00),\n(2, 'Electronics', 2000.00),\n(3, 'Clothing', 800.00),\n(3, 'Clothing', 1200.00),\n(4, 'Clothing', 1500.00);<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">sales<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    employee_id <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    department <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">50<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    sale_amount <\/span><span style=\"color: #C678DD\">DECIMAL<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> sales <\/span><span style=\"color: #C678DD\">VALUES<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Electronics&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">1000<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Electronics&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">1500<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Electronics&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">2000<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">3<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Clothing&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">800<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">3<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Clothing&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">1200<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">4<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Clothing&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">1500<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Now, let&#8217;s rank employees within each department based on their sale amounts:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    employee_id,\n    department,\n    sale_amount,\n    RANK() OVER (\n        PARTITION BY department\n        ORDER BY sale_amount DESC\n    ) AS rank_in_department\nFROM \n    sales;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    employee_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    department,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    sale_amount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">RANK<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> department<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> sale_amount <\/span><span style=\"color: #C678DD\">DESC<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> rank_in_department<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    sales;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query will produce:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>employee_id | department  | sale_amount | rank_in_department\n------------|-------------|-------------|--------------------\n2           | Electronics | 2000.00     | 1\n1           | Electronics | 1500.00     | 2\n1           | Electronics | 1000.00     | 3\n4           | Clothing    | 1500.00     | 1\n3           | Clothing    | 1200.00     | 2\n3           | Clothing    | 800.00      | 3<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">employee_id | department  | sale_amount | rank_in_department<\/span><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">------------|-------------|-------------|--------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">           | Electronics | <\/span><span style=\"color: #D19A66\">2000<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">     | <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">           | Electronics | <\/span><span style=\"color: #D19A66\">1500<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">     | <\/span><span style=\"color: #D19A66\">2<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">           | Electronics | <\/span><span style=\"color: #D19A66\">1000<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">     | <\/span><span style=\"color: #D19A66\">3<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">4<\/span><span style=\"color: #ABB2BF\">           | Clothing    | <\/span><span style=\"color: #D19A66\">1500<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">     | <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">3<\/span><span style=\"color: #ABB2BF\">           | Clothing    | <\/span><span style=\"color: #D19A66\">1200<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">     | <\/span><span style=\"color: #D19A66\">2<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">3<\/span><span style=\"color: #ABB2BF\">           | Clothing    | <\/span><span style=\"color: #D19A66\">800<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">      | <\/span><span style=\"color: #D19A66\">3<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s what&#8217;s happening:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>The <code>PARTITION BY department<\/code> clause splits the data into separate partitions for each department.<\/li>\n\n\n\n<li>Within each partition, <code>ORDER BY sale_amount DESC<\/code> sorts the rows by sale amount in descending order.<\/li>\n\n\n\n<li><code>RANK()<\/code> assigns a rank to each row within its partition based on this order.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">RANK() vs. DENSE_RANK() vs. ROW_NUMBER()<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">While we&#8217;re on the topic, it&#8217;s worth discussing two related functions: <code>DENSE_RANK()<\/code> and <code>ROW_NUMBER()<\/code>. These functions are similar to <code>RANK()<\/code> but behave slightly differently:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>RANK()<\/code>: Assigns the same rank to ties, skipping the next rank(s).<\/li>\n\n\n\n<li><code>DENSE_RANK()<\/code>: Also assigns the same rank to ties, but doesn&#8217;t skip the next rank.<\/li>\n\n\n\n<li><code>ROW_NUMBER()<\/code>: Assigns a unique number to each row, regardless of ties.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s see them in action:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    employee_id,\n    department,\n    sale_amount,\n    RANK() OVER (ORDER BY sale_amount DESC) AS rank,\n    DENSE_RANK() OVER (ORDER BY sale_amount DESC) AS dense_rank,\n    ROW_NUMBER() OVER (ORDER BY sale_amount DESC) AS row_number\nFROM \n    sales;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    employee_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    department,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    sale_amount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">RANK<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> sale_amount <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> rank,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">DENSE_RANK<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> sale_amount <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> dense_rank,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">ROW_NUMBER<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> sale_amount <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> row_number<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    sales;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query might produce:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>employee_id | department  | sale_amount | rank | dense_rank | row_number\n------------|-------------|-------------|------|------------|------------\n2           | Electronics | 2000.00     | 1    | 1          | 1\n1           | Electronics | 1500.00     | 2    | 2          | 2\n4           | Clothing    | 1500.00     | 2    | 2          | 3\n3           | Clothing    | 1200.00     | 4    | 3          | 4\n1           | Electronics | 1000.00     | 5    | 4          | 5\n3           | Clothing    | 800.00      | 6    | 5          | 6<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">employee_id | department  | sale_amount | rank | dense_rank | row_number<\/span><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">------------|-------------|-------------|------|------------|------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">           | Electronics | <\/span><span style=\"color: #D19A66\">2000<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">     | <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">    | <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">          | <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">           | Electronics | <\/span><span style=\"color: #D19A66\">1500<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">     | <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">    | <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">          | <\/span><span style=\"color: #D19A66\">2<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">4<\/span><span style=\"color: #ABB2BF\">           | Clothing    | <\/span><span style=\"color: #D19A66\">1500<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">     | <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">    | <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">          | <\/span><span style=\"color: #D19A66\">3<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">3<\/span><span style=\"color: #ABB2BF\">           | Clothing    | <\/span><span style=\"color: #D19A66\">1200<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">     | <\/span><span style=\"color: #D19A66\">4<\/span><span style=\"color: #ABB2BF\">    | <\/span><span style=\"color: #D19A66\">3<\/span><span style=\"color: #ABB2BF\">          | <\/span><span style=\"color: #D19A66\">4<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">           | Electronics | <\/span><span style=\"color: #D19A66\">1000<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">     | <\/span><span style=\"color: #D19A66\">5<\/span><span style=\"color: #ABB2BF\">    | <\/span><span style=\"color: #D19A66\">4<\/span><span style=\"color: #ABB2BF\">          | <\/span><span style=\"color: #D19A66\">5<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D19A66\">3<\/span><span style=\"color: #ABB2BF\">           | Clothing    | <\/span><span style=\"color: #D19A66\">800<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">00<\/span><span style=\"color: #ABB2BF\">      | <\/span><span style=\"color: #D19A66\">6<\/span><span style=\"color: #ABB2BF\">    | <\/span><span style=\"color: #D19A66\">5<\/span><span style=\"color: #ABB2BF\">          | <\/span><span style=\"color: #D19A66\">6<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Notice how <code>RANK()<\/code> skips rank 3 after the tie at rank 2, while <code>DENSE_RANK()<\/code> doesn&#8217;t skip any ranks. <code>ROW_NUMBER()<\/code> simply assigns a unique number to each row.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Real-World Applications<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Now that we understand how <code>RANK() OVER (PARTITION BY)<\/code> works, let&#8217;s explore some real-world scenarios where it shines.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Identifying Top Performers<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In sales, you might want to identify the top performer in each region:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT *\nFROM (\n    SELECT \n        employee_id,\n        region,\n        sales_total,\n        RANK() OVER (PARTITION BY region ORDER BY sales_total DESC) AS rank\n    FROM \n        yearly_sales\n) ranked\nWHERE \n    rank = 1;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> *<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        employee_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        region,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        sales_total,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">RANK<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> region <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> sales_total <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> rank<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        yearly_sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">) ranked<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    rank <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query ranks employees within each region based on their total sales and then selects only the top-ranked employee from each region.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Percentile Calculations<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><code>RANK()<\/code> can be used to calculate percentiles. For example, to find which products are in the top 10% by revenue:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    product_id,\n    product_name,\n    revenue,\n    percent_rank\nFROM (\n    SELECT \n        product_id,\n        product_name,\n        revenue,\n        PERCENT_RANK() OVER (ORDER BY revenue DESC) AS percent_rank\n    FROM \n        product_sales\n) ranked\nWHERE \n    percent_rank &lt;= 0.1;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    product_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    product_name,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    revenue,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    percent_rank<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        product_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        product_name,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        revenue,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">PERCENT_RANK<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> revenue <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> percent_rank<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        product_sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">) ranked<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    percent_rank <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query uses <code>PERCENT_RANK()<\/code>, which is based on <code>RANK()<\/code>, to calculate each product&#8217;s percentile rank by revenue.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Time Series Analysis<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In time series data, you might want to compare each data point with the previous one:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT \n    date,\n    value,\n    value - LAG(value) OVER (ORDER BY date) AS daily_change,\n    RANK() OVER (ORDER BY value - LAG(value) OVER (ORDER BY date) DESC) AS rank_by_increase\nFROM \n    daily_metrics;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">date<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">value<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">value<\/span><span style=\"color: #ABB2BF\"> - <\/span><span style=\"color: #56B6C2\">LAG<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">value<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">date<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> daily_change,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">RANK<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">value<\/span><span style=\"color: #ABB2BF\"> - <\/span><span style=\"color: #56B6C2\">LAG<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">value<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">date<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> rank_by_increase<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    daily_metrics;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query calculates the day-over-day change and ranks days by their increase.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-text-align-center wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\"><strong style=\"background-color: rgb(69, 136, 216); color: rgb(241, 235, 218); font-size: calc(12px + 0.390625vw); font-weight: bold;\">RANK () OVER (PARTITION BY) with a Rapid Database Builder<\/strong><\/mark><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When working with SQL, executing efficient queries is straightforward, but sometimes complex operations, like ranking data within specific groups, require extra attention.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is where rapid database builders like Five come into play. Five provides a MySQL database for your application and generates an automatic UI, making it easier to interact with and manage your data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">With Five, you can handle tasks like using <code>RANK() OVER (PARTITION BY ...)<\/code> in SQL intuitively through generated forms and tables.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five allows you to create interactive forms, tables, dynamic charts, comprehensive PDF reports, and dashboards that are automatically generated based on your database schema. This makes it easier to visualize and analyze the results of your ranking operations.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Additionally, Five lets you write custom JavaScript and TypeScript functions, providing the flexibility to implement custom business logic that can interact with your ranked data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Once your application is ready, Five makes deployment easy with just a few clicks, allowing you to deploy your MySQL-based application to a secure, scalable cloud infrastructure. This lets you focus on developing, while Five handles the complexities of cloud deployment and infrastructure management.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;re serious about using SQL efficiently, give Five a try.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access to Five\u2019s online development environment and start building your MySQL web application today.<\/a><\/p>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);\"><strong>Build Your Database In 3 Steps<\/strong><br \/><span style=\"font-size: 14pt;\">Start Developing Today<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\" data-wplink-edit=\"true\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Instant Access<\/strong><\/button><br \/><\/a><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Performance Considerations<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">While <code>RANK() OVER (PARTITION BY)<\/code> is a useful tool, it&#8217;s important to consider its performance implications, especially on large datasets.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Indexing<\/strong>: Ensure that the columns used in the <code>PARTITION BY<\/code> and <code>ORDER BY<\/code> clauses are properly indexed.<\/li>\n\n\n\n<li><strong>Materialized Views<\/strong>: For frequently run queries, consider using materialized views to pre-compute rankings.<\/li>\n\n\n\n<li><strong>Partitioning<\/strong>: If your database supports it, table partitioning can significantly speed up queries that use <code>PARTITION BY<\/code>.<\/li>\n\n\n\n<li><strong>Limiting Results<\/strong>: If you only need the top N ranks, use a subquery with a <code>WHERE<\/code> clause to limit the results.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Common Pitfalls and How to Avoid Them<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">When working with <code>RANK() OVER (PARTITION BY)<\/code>, there are a few common mistakes to watch out for:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Forgetting ORDER BY<\/strong>: Without an <code>ORDER BY<\/code> clause, all rows in a partition will have the same rank.<\/li>\n\n\n\n<li><strong>Misunderstanding Ties<\/strong>: Remember that <code>RANK()<\/code> will assign the same rank to tied values and skip the next rank(s).<\/li>\n\n\n\n<li><strong>Incorrect Partitioning<\/strong>: Make sure you&#8217;re partitioning on the correct column(s) for your analysis.<\/li>\n\n\n\n<li><strong>Overuse<\/strong>: While powerful, overusing window functions can lead to complex, hard-to-maintain queries. Sometimes a simple GROUP BY might suffice.<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">FAQ<\/h2>\n\n\n<div id=\"rank-math-faq\" class=\"rank-math-block\">\n<ol class=\"rank-math-list \">\n<li id=\"faq-question-1771985858796\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong>What does RANK() OVER (PARTITION BY) do in SQL?<\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p><code>RANK() OVER (PARTITION BY)<\/code> is a window function that assigns a ranking number to each row within a specific group (partition) of data. The <code>PARTITION BY<\/code> clause splits the dataset into groups, and the ranking restarts for each group, while the <code>ORDER BY<\/code> clause determines how the rows are ranked within that group.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771986137117\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong>How is RANK() different from ROW_NUMBER() and DENSE_RANK()?<\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>All three are ranking functions, but they handle ties differently:<br \/><strong>RANK()<\/strong> \u2192 Gives the same rank to tied rows and skips the next rank.<br \/><strong>DENSE_RANK()<\/strong> \u2192 Gives the same rank to ties but does not skip numbers.<br \/><strong>ROW_NUMBER()<\/strong> \u2192 Assigns a unique sequential number regardless of ties.<br \/>This difference is important when you need either continuous rankings or unique row identifiers.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771986151686\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong>When should you use <code>PARTITION BY<\/code> with <code>RANK()<\/code>?<\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>You should use <code>PARTITION BY<\/code> when you want rankings calculated separately within categories or groups, such as ranking employees by sales within each department or products by revenue within each region. Without it, the ranking is applied across the entire dataset.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771986181367\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong>What happens if there are ties when using RANK()?<\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>If multiple rows have the same value in the <code>ORDER BY<\/code> column, they receive the same rank. The next rank is then skipped, creating gaps in the sequence (for example, 1, 2, 2, 4). This behavior distinguishes <code>RANK()<\/code> from <code>DENSE_RANK()<\/code>.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771986192369\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong>What are common use cases for <code>RANK() OVER (PARTITION BY)<\/code>?<\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>Typical use cases include:<br \/>Identifying top performers in each group (e.g., best-selling products per category)<br \/>Creating leaderboards or performance comparisons<br \/>Performing percentile or time-series analysis<br \/>These scenarios benefit from ranking data within logical subsets rather than across the entire table.<\/p>\n\n<\/div>\n<\/li>\n<\/ol>\n<\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n<\/div><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><code>RANK() OVER (PARTITION BY)<\/code> is a versatile SQL function that can greatly enhance your data analysis capabilities. By understanding how it works and where to apply it, you can write more efficient queries and gain deeper insights from your data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access to Five\u2019s online development environment and start building your MySQL web application today.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Breakdown of RANK () OVER (PARTITION BY) SQL&#8217;s window functions are great for data analysis, and RANK() OVER (PARTITION BY) stands out as a particularly useful tool. This article will explore how this function works, and its practical applications. SQL Made Simple &#8211; Try FiveFive is the Easiest Way to Create, Model and Query a [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4069,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-4068","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-RANK-OVER-PARTITION-BY-in-SQL.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-RANK-OVER-PARTITION-BY-in-SQL-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-RANK-OVER-PARTITION-BY-in-SQL-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-RANK-OVER-PARTITION-BY-in-SQL-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-RANK-OVER-PARTITION-BY-in-SQL-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-RANK-OVER-PARTITION-BY-in-SQL.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-RANK-OVER-PARTITION-BY-in-SQL.jpg",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"Breakdown of RANK () OVER (PARTITION BY) SQL&#8217;s window functions are great for data analysis, and RANK() OVER (PARTITION BY) stands out as a particularly useful tool. This article will explore how this function works, and its practical applications. SQL Made Simple - Try FiveFive is the Easiest Way to Create, Model and Query a&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4068","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=4068"}],"version-history":[{"count":2,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4068\/revisions"}],"predecessor-version":[{"id":17813,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4068\/revisions\/17813"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4069"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4068"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4068"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4068"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}