RANK

RANK calculates the rank of a value in a group of values. RANK calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. Rows with equal values for the ranking criteria receive the same rank. Database system adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.

DENSE_RANK

DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank.

RANK function in Universe Designer

RANK() OVER (PARTITION BY () ORDER BY ())

PARTITION BY () – you can mention any of your dimension object for which you wanted Rank and should be grouped for some of measures. This is an option parameter in RANK.

ORDER BY () – RANK will be calculated based on Measure / Dimension object along with ASC / DESC order. This parameter is must for RANK function.

Example: To get the Ranking for State wise Sales amount, below is the sample data as is.

Create a Ranking report for State wise based on Sales amount.

Example 1: RANK() OVER (ORDER BY (Sum(SalesAmount)) DESC)

RANK function would consider the SalesAmount in Descending order for each State and return the RANK numbers. Distinct amount values are considered as same RANK numbers. In the below example, your report SQL would be,

Select Country_name, State, Sum(salesAmount), RANK() OVER (ORDER BY (Sum(SalesAmount)) DESC) rank from SalesTable group by Country_name, State
RANK function won’t display the numbers consecutively if two or more values are same and those values are considered as same RANK. Remaining RANK numbers will be skipped for the number of Rows which has same values. In the above example, RANK 3 is having 2 rows due to same amount and RANK 4 is skipped and next RANK 5 is displayed.

Example 2: RANK() OVER (ORDER BY State)

RANK function would consider the State object as order and return the RANK numbers for each State & each row. Each row will have an separate RANK and same State repeats multiple rows, those rows will have same RANK numbers.

Select Country_name, State, Product_category,Sum(salesAmount), RANK() OVER (ORDER BY State) rank from SalesTable group by Country_name, State, , Product_category
Example 3:  RANK() OVER  (PARTITION BY (Sate, Product_Category) ORDER BY (Sum(SalesAmount)) DESC)

In this case RANK will be calculated based on State & Product Category for the Sum of Sales Amount.

DENSE RANK function in Universe Designer

DENSE_RANK() OVER (PARTITION BY () ORDER BY ())

PARTITION BY () – you can mention any of your dimension object for which you wanted Rank and should be grouped for some of measures.

ORDER BY () – DENSE_RANK will be calculated based on Measure / Dimension object along with ASC / DESC order.

Example 1: DENSE_RANK() OVER (ORDER BY (Sum(SalesAmount)) DESC)

DESNE_RANK function would consider the SalesAmount in Descending order for each State and return the RANK numbers. Distinct amount values are considered as same RANK numbers and next rows will have a consecutive RANK numbers. In the below example, your report SQL would be,

Select Country_name, State, Sum(salesAmount), DENSE_RANK() OVER (PARTITION BY (Sate) ORDER BY (Sum(SalesAmount)) DESC) D_Rank from SalesTable group by Country_name, State
DENSE_RANK function will display the numbers consecutively if two or more values are same and those values are considered as same RANK. Remaining RANK numbers will be considered for next Rows which has same values. In the above example, RANK 3 is having 2 rows due to same amount and RANK 4 also having 2 rows and these won’t be any RANK number skipped in DENSE RANK and next RANK 5 is displayed.

DENSE_RANK can be used whenever you r report need consecutive RANK numbers to identify the range of PRODUCTs. DENSE RANK wont skip nay RANK numbers if there is same values are occurred and you will get consecutive numbers in you report and you can decide either to use RANK or DENSE_RANK based on your needs.