{"id":1259,"date":"2019-02-21T09:23:37","date_gmt":"2019-02-21T02:23:37","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1259"},"modified":"2020-07-11T04:01:19","modified_gmt":"2020-07-10T21:01:19","slug":"sql-server-dense_rank-function","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-window-functions\/sql-server-dense_rank-function\/","title":{"rendered":"SQL Server DENSE_RANK Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server DENSE_RANK()<\/code> function to assign a rank to each row within a partition of a result set, with no gaps in ranking values.<\/p>\n\n\n\n

Introduction to SQL Server DENSE_RANK() function #<\/a><\/h2>\n\n\n\n

The DENSE_RANK()<\/code> is a window function<\/a> that assigns a rank to each row within a partition of a result set. Unlike the RANK()<\/code><\/a> function, the DENSE_RANK()<\/code> function returns consecutive rank values. Rows in each partition receive the same ranks if they have the same values.<\/p>\n\n\n\n

The syntax of the DENSE_RANK()<\/code> function is as follows:<\/p>\n\n\n

DENSE_RANK() OVER (\n    [PARTITION BY partition_expression, ... ]\n    ORDER BY sort_expression [ASC | DESC], ...\n)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The DENSE_RANK(<\/code>) function is applied to the rows of each partition defined by the PARTITION BY<\/code> clause, in a specified order, defined by ORDER BY<\/code> clause. It resets the rank when the partition boundary is crossed.<\/p>\n\n\n\n
The PARITION BY<\/code> clause is optional. If you omit it, the function will treat the whole result set as a single partition.<\/p>\n\n\n\n
SQL Server DENSE_RANK() function illustration #<\/a><\/h2>\n\n\n\n
The following statements create a new table<\/a> named dense_rank_demo<\/code> and insert<\/a> some rows into that table:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales.dense_rank_demo (\n\tv VARCHAR<\/span>(10<\/span>)\n);\n\t\nINSERT<\/span> INTO<\/span> sales.dense_rank_demo(v)\nVALUES<\/span>('A'<\/span>),('B'<\/span>),('B'<\/span>),('C'<\/span>),('C'<\/span>),('D'<\/span>),('E'<\/span>);\n\t\nSELECT<\/span> * FROM<\/span> sales.dense_rank_demo;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement uses both DENSE_RANK()<\/code> and RANK()<\/code> functions to assign a rank to each row of the result set:<\/p>\n\n\n
SELECT<\/span>\n\tv,\n\tDENSE_RANK<\/span>() OVER<\/span> (\n\t\tORDER<\/span> BY<\/span> v\n\t) my_dense_rank,\n\tRANK<\/span>() OVER<\/span> (\n\t\tORDER<\/span> BY<\/span> v\n\t) my_rank\nFROM<\/span>\n\tsales.dense_rank_demo;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
SQL Server DENSE_RANK() function examples #<\/a><\/h2>\n\n\n\n
We will use the production.products<\/code> table to demonstrate the DENSE_RANK()<\/code> function:<\/p>\n\n\n\n
\"products\"<\/figure>\n\n\n\n
Using SQL Server DENSE_RANK() over a result set example #<\/a><\/h3>\n\n\n\n
The following example uses the DENSE_RANK()<\/code> function to rank products by list prices:<\/p>\n\n\n
SELECT<\/span>\n\tproduct_id,\n\tproduct_name,\n\tlist_price,\n\tDENSE_RANK<\/span> () OVER<\/span> ( \n\t\tORDER<\/span> BY<\/span> list_price DESC<\/span>\n\t) price_rank \nFROM<\/span>\n\tproduction.products;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Using SQL Server DENSE_RANK() over partitions example #<\/a><\/h3>\n\n\n\n
The following statement ranks products in each category by list prices. It returns only the top 3 products per category by list prices.<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> (\n\tSELECT<\/span>\n\t\tproduct_id,\n\t\tproduct_name,\n\t\tcategory_id,\n\t\tlist_price,\n\t\tDENSE_RANK<\/span> () OVER<\/span> ( \n\t\t\tPARTITION<\/span> BY<\/span> category_id\n\t\t\tORDER<\/span> BY<\/span> list_price DESC<\/span>\n\t\t) price_rank \n\tFROM<\/span>\n\t\tproduction.products\n) t\nWHERE<\/span> price_rank < 3<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the SQL Server DENSE_RANK()<\/code> function to assign a rank to each row within a partition of a result set, with no gaps in rank values.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t