​
Overview

The DENSE_RANK() window function assigns a rank for each value within a specified group, based on the ORDER BY expression in the OVER clause. It can be used with all data types supported by Oxla.

​
Syntax

The syntax for this function is as follows:

DENSE_RANK() OVER (ORDER BY order_list)

The output type for this function is a BIGINT and it indicates the rank of values in a table, regardless of the input types. If the ORDER BY expression is omitted, all ranks will default to 1. In case an optional PARTITION BY expression is included, the rankings are reset for each group of rows. The rows with equal values for the ranking criteria receive the same rank.

Unlike RANK() function, there is no gap in the sequence of ranked values (if two rows are ranked 1, the next rank will be 2)

​
Parameters

  • (): the function takes no parameters, but empty parentheses is required

​
Examples

For the needs of this section, we will create a winsales table that stores information about some sales transactions:

CREATE TABLE winsales(
  salesid int,
  dateid date,
  sellerid int,
  buyerid text,
  qty int,
  qty_shipped int);
INSERT INTO winsales VALUES
    (30001, '8/2/2003', 3, 'b', 10, 10),
    (10001, '12/24/2003', 1, 'c', 10, 10),
    (10005, '12/24/2003', 1, 'a', 30, null),
    (40001, '1/9/2004', 4, 'a', 40, null),
    (10006, '1/18/2004', 1, 'c', 10, null),
    (20001, '2/12/2004', 2, 'b', 20, 20),
    (40005, '2/12/2004', 4, 'a', 10, 10),
    (20002, '2/16/2004', 2, 'c', 20, 20),
    (30003, '4/18/2004', 3, 'b', 15, null),
    (30004, '4/18/2004', 3, 'b', 20, null),
    (30007, '9/7/2004', 3, 'c', 30, null);

​
DENSE_RANK() with ORDER BY

In this example we will focus on executing the DENSE_RANK() function with ORDER BY keyword and calculate the descending dense rank of all rows based on the quantity sold:

SELECT salesid, qty
  Dense_RANK() OVER (ORDER BY qty DESC) AS d_rnk
  RANK() OVER (ORDER BY qty DESC) AS rnk
FROM winsales
ORDER BY 2,1;

Here is the output for the query presented above that includes the sales ID along with the quantity sold and both dense and regular ranks:

  salesid | qty | d_rnk | rnk 
---------+-----+-------+-----
   10001 |  10 |     5 |   8
   10006 |  10 |     5 |   8
   30001 |  10 |     5 |   8
   40005 |  10 |     5 |   8
   30003 |  15 |     4 |   7
   20001 |  20 |     3 |   4
   20002 |  20 |     3 |   4
   30004 |  20 |     3 |   4
   10005 |  30 |     2 |   2
   30007 |  30 |     2 |   2
   40001 |  40 |     1 |   1
(11 rows)

​
DENSE_RANK() with ORDER BY and PARTITION_BY

In this example we will focus on executing the DENSE_RANK() function with ORDER BY keyword and PARTITION BY clause and partition the table by seller ID, then order each partition by the quantity and assign a dense rank to each row:

SELECT salesid, sellerid, qty
  DENSE_RANK() OVER (PARTITION BY sellerid ORDER BY qty DESC) AS d_rnk
FROM winsales
ORDER BY 2,3,1;

Here is the output for the query presented above:

 salesid | sellerid | qty | d_rnk 
---------+----------+-----+-------
   10001 |        1 |  10 |     2
   10006 |        1 |  10 |     2
   10005 |        1 |  30 |     1
   20001 |        2 |  20 |     1
   20002 |        2 |  20 |     1
   30001 |        3 |  10 |     4
   30003 |        3 |  15 |     3
   30004 |        3 |  20 |     2
   30007 |        3 |  30 |     1
   40005 |        4 |  10 |     2
   40001 |        4 |  40 |     1
(11 rows)