{"id":106,"date":"2026-03-06T08:30:16","date_gmt":"2026-03-06T08:30:16","guid":{"rendered":"https:\/\/gigz.pk\/sql\/?post_type=lesson&#038;p=106"},"modified":"2026-03-16T18:54:03","modified_gmt":"2026-03-16T18:54:03","slug":"rank-dense_rank","status":"publish","type":"lesson","link":"https:\/\/gigz.pk\/sql\/lesson\/rank-dense_rank\/","title":{"rendered":"RANK, DENSE_RANK"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">In SQL, <strong>ranking functions<\/strong> allow you to assign a rank to each row within a result set based on the values in one or more columns. These are commonly used to find top-performing items, order data by score, or identify relative positions. Two of the most frequently used ranking functions are <strong>RANK<\/strong> and <strong>DENSE_RANK<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">1. RANK Function<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The <strong>RANK<\/strong> function assigns a unique rank to each distinct value within a result set. If multiple rows have the same value, they receive the same rank. However, the next rank will skip values accordingly.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Syntax:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">RANK() OVER (<br>    [PARTITION BY partition_column]<br>    ORDER BY order_column [ASC | DESC]<br>)<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Explanation:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>PARTITION BY<\/code>: Divides the data into groups and ranks each group separately. Optional.<\/li>\n\n\n\n<li><code>ORDER BY<\/code>: Specifies the column(s) to rank by. Mandatory.<\/li>\n\n\n\n<li>Rows with identical values get the same rank.<\/li>\n\n\n\n<li>Ranks are not consecutive if ties occur.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example:<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Suppose we have a table of students with their scores:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Student   Score<br>Alice     95<br>Bob       95<br>Charlie   90<br>David     85<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Query:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT<br>    Student,<br>    Score,<br>    RANK() OVER (ORDER BY Score DESC) AS Rank<br>FROM Students;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Student   Score   Rank<br>Alice     95      1<br>Bob       95      1<br>Charlie   90      3<br>David     85      4<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Notice that the rank jumps from 1 to 3 after the tie.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">2. DENSE_RANK Function<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The <strong>DENSE_RANK<\/strong> function works similarly to RANK but does not leave gaps between ranks when there are ties. Rows with the same value still share the same rank, but the next rank is consecutive.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Syntax:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DENSE_RANK() OVER (<br>    [PARTITION BY partition_column]<br>    ORDER BY order_column [ASC | DESC]<br>)<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Explanation:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Like RANK, you can partition data.<\/li>\n\n\n\n<li>Identical values get the same rank.<\/li>\n\n\n\n<li>Ranks are consecutive, even after ties.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example:<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Using the same table:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT<br>    Student,<br>    Score,<br>    DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank<br>FROM Students;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Student   Score   DenseRank<br>Alice     95      1<br>Bob       95      1<br>Charlie   90      2<br>David     85      3<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Notice how the ranks are consecutive, unlike RANK.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Key Differences<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>RANK<\/strong>: Leaves gaps in ranking when ties occur.<\/li>\n\n\n\n<li><strong>DENSE_RANK<\/strong>: No gaps; ranks are consecutive.<\/li>\n\n\n\n<li>Both can use <strong>PARTITION BY<\/strong> to rank within groups.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example with Partition:<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Suppose we have scores by class:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Class   Student   Score<br>A       Alice     95<br>A       Bob       95<br>A       Charlie   90<br>B       David     85<br>B       Eve       80<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Query with partition:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT<br>    Class,<br>    Student,<br>    Score,<br>    RANK() OVER (PARTITION BY Class ORDER BY Score DESC) AS Rank,<br>    DENSE_RANK() OVER (PARTITION BY Class ORDER BY Score DESC) AS DenseRank<br>FROM Students;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Class   Student   Score   Rank   DenseRank<br>A       Alice     95      1      1<br>A       Bob       95      1      1<br>A       Charlie   90      3      2<br>B       David     85      1      1<br>B       Eve       80      2      2<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use <strong>RANK<\/strong> when you want the actual ranking positions, including gaps after ties.<\/li>\n\n\n\n<li>Use <strong>DENSE_RANK<\/strong> when you want consecutive rankings, ignoring gaps caused by ties.<\/li>\n\n\n\n<li>Both functions are helpful for leaderboards, top-n queries, and group-based ranking.<\/li>\n<\/ul>\n\n\n<div class=\"yoast-breadcrumbs\"><span><span><a href=\"https:\/\/gigz.pk\/sql\/\">Home<\/a><\/span> \u00bb <span class=\"breadcrumb_last\" aria-current=\"page\">Learn Advanced SQL &#038; Database Engineering (SQL-301) > Advanced Querying > RANK, DENSE_RANK<\/span><\/span><\/div>\n\n\n<div class=\"schema-faq wp-block-yoast-faq-block\"><div class=\"schema-faq-section\" id=\"faq-question-1773596752006\"><strong class=\"schema-faq-question\"><\/strong> <p class=\"schema-faq-answer\"><\/p> <\/div> <\/div>\n","protected":false},"menu_order":51,"template":"","class_list":["post-106","lesson","type-lesson","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.6 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>RANK, DENSE_RANK - SQL Learning Hub<\/title>\n<meta name=\"description\" content=\"Learn SQL RANK and DENSE_RANK functions with clear examples. Understand SQL ranking, window functions, and partition-based ranking queries.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/gigz.pk\/sql\/lesson\/rank-dense_rank\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"RANK, DENSE_RANK - SQL Learning Hub\" \/>\n<meta property=\"og:description\" content=\"Learn SQL RANK and DENSE_RANK functions with clear examples. Understand SQL ranking, window functions, and partition-based ranking queries.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/gigz.pk\/sql\/lesson\/rank-dense_rank\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Learning Hub\" \/>\n<meta property=\"article:modified_time\" content=\"2026-03-16T18:54:03+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":[\"WebPage\",\"FAQPage\"],\"@id\":\"https:\\\/\\\/gigz.pk\\\/sql\\\/lesson\\\/rank-dense_rank\\\/\",\"url\":\"https:\\\/\\\/gigz.pk\\\/sql\\\/lesson\\\/rank-dense_rank\\\/\",\"name\":\"RANK, DENSE_RANK - SQL Learning Hub\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/gigz.pk\\\/sql\\\/#website\"},\"datePublished\":\"2026-03-06T08:30:16+00:00\",\"dateModified\":\"2026-03-16T18:54:03+00:00\",\"description\":\"Learn SQL RANK and DENSE_RANK functions with clear examples. Understand SQL ranking, window functions, and partition-based ranking queries.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/gigz.pk\\\/sql\\\/lesson\\\/rank-dense_rank\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/gigz.pk\\\/sql\\\/lesson\\\/rank-dense_rank\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/gigz.pk\\\/sql\\\/lesson\\\/rank-dense_rank\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/gigz.pk\\\/sql\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Learn Advanced SQL & Database Engineering (SQL-301) > Advanced Querying > RANK, DENSE_RANK\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/gigz.pk\\\/sql\\\/#website\",\"url\":\"https:\\\/\\\/gigz.pk\\\/sql\\\/\",\"name\":\"SQL Learning Hub\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/gigz.pk\\\/sql\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"RANK, DENSE_RANK - SQL Learning Hub","description":"Learn SQL RANK and DENSE_RANK functions with clear examples. Understand SQL ranking, window functions, and partition-based ranking queries.","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:\/\/gigz.pk\/sql\/lesson\/rank-dense_rank\/","og_locale":"en_US","og_type":"article","og_title":"RANK, DENSE_RANK - SQL Learning Hub","og_description":"Learn SQL RANK and DENSE_RANK functions with clear examples. Understand SQL ranking, window functions, and partition-based ranking queries.","og_url":"https:\/\/gigz.pk\/sql\/lesson\/rank-dense_rank\/","og_site_name":"SQL Learning Hub","article_modified_time":"2026-03-16T18:54:03+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":["WebPage","FAQPage"],"@id":"https:\/\/gigz.pk\/sql\/lesson\/rank-dense_rank\/","url":"https:\/\/gigz.pk\/sql\/lesson\/rank-dense_rank\/","name":"RANK, DENSE_RANK - SQL Learning Hub","isPartOf":{"@id":"https:\/\/gigz.pk\/sql\/#website"},"datePublished":"2026-03-06T08:30:16+00:00","dateModified":"2026-03-16T18:54:03+00:00","description":"Learn SQL RANK and DENSE_RANK functions with clear examples. Understand SQL ranking, window functions, and partition-based ranking queries.","breadcrumb":{"@id":"https:\/\/gigz.pk\/sql\/lesson\/rank-dense_rank\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/gigz.pk\/sql\/lesson\/rank-dense_rank\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/gigz.pk\/sql\/lesson\/rank-dense_rank\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/gigz.pk\/sql\/"},{"@type":"ListItem","position":2,"name":"Learn Advanced SQL & Database Engineering (SQL-301) > Advanced Querying > RANK, DENSE_RANK"}]},{"@type":"WebSite","@id":"https:\/\/gigz.pk\/sql\/#website","url":"https:\/\/gigz.pk\/sql\/","name":"SQL Learning Hub","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/gigz.pk\/sql\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/gigz.pk\/sql\/wp-json\/wp\/v2\/lesson\/106","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gigz.pk\/sql\/wp-json\/wp\/v2\/lesson"}],"about":[{"href":"https:\/\/gigz.pk\/sql\/wp-json\/wp\/v2\/types\/lesson"}],"wp:attachment":[{"href":"https:\/\/gigz.pk\/sql\/wp-json\/wp\/v2\/media?parent=106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}