T-SQL/MSBI Knowledge Share Videos

Basic Ranking function

Following are the basic ranking functions normally used when coding:
  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()
These ranking functions are applied based on some columns, like two want the row number based on one column or multiple column. Lets look into following examples:

IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='TSQL_16_RANK_FUNCTION')
DROP TABLE TSQL_16_RANK_FUNCTION
GO

CREATE TABLE dbo.TSQL_16_RANK_FUNCTION
(
      [ID] [int] NULL,
      [NAME] [varchar](90)  NULL,
      [LOCATION] [varchar](70)
)
GO

INSERT INTO TSQL_16_RANK_FUNCTION VALUES(1,'AAA','hpt')
INSERT INTO TSQL_16_RANK_FUNCTION VALUES(1,'AAA','hpt')
INSERT INTO TSQL_16_RANK_FUNCTION VALUES(2,'BBB','Dvg')
INSERT INTO TSQL_16_RANK_FUNCTION VALUES(2,'BBB','Dvg')
INSERT INTO TSQL_16_RANK_FUNCTION VALUES(3,'CCC','bng')
INSERT INTO TSQL_16_RANK_FUNCTION VALUES(3,'CCC','bng')

SELECT * FROM dbo.TSQL_16_RANK_FUNCTION

ROW_NUMBER()

Without partition:

SELECT ID,NAME,LOCATION,ROW_NUMBER() OVER(ORDER BY ID) AS ROW_NUMBER FROM TSQL_16_RANK_FUNCTION

The above query returns sequence number for the record. 

With partition:

SELECT ID,NAME,LOCATION,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY ID) AS ROW_NUMBER FROM TSQL_16_RANK_FUNCTION

The above query will partition the records based on NAME column (based on similar name) and considers it as set of records and applies the sequence number for each set of records. In the first query no partition is specified and hence each record is considered as one set of record.

RANK()

Without partition:

SELECT ID,NAME,LOCATION,RANK() OVER(ORDER BY ID) AS RANK FROM TSQL_16_RANK_FUNCTION


In case of RANK without partition, RANK is applied based on the ORDER column. We can see in above example that order is done on ID column so for two 1's RANK is 1 then for two 2's its 3. Refer the number assigned in above diagram.

With partition:

SELECT ID,NAME,LOCATION,RANK() OVER(PARTITION BY ID ORDER BY LOCATION DESC) AS RANK FROM TSQL_16_RANK_FUNCTION










In the above example with partition RANK is applied on set of records. group the similar column and apply the RANK function. But in the ID value 3 we can see 1,2,2. Since partition is done one ID it groups as one set and then order by is done on LOCATION so number is assigned so.

DENSE_RANK()

Without partition:

SELECT ID,NAME,LOCATION,DENSE_RANK() OVER(ORDER BY ID) AS DENSE_RANK FROM TSQL_16_RANK_FUNCTION


In the DENSE_RANK i wont jump the degit as in RANK function. Just look at the examples of both you can find the difference.

With partition:

SELECT ID,NAME,LOCATION,DENSE_RANK() OVER(PARTITION BY ID ORDER BY LOCATION DESC) AS DENSE_RANK FROM TSQL_16_RANK_FUNCTION











In the above example based on partition and order DENSE_RANK function is applied.

Summary:


SELECT *,
ROW_NUMBER() OVER(ORDER BY ID) AS ROW_NUMBER_WOP,
ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY ID) AS ROW_NUMBER_WP,
RANK() OVER(ORDER BY ID) AS RANK_WOP,
RANK() OVER(PARTITION BY ID ORDER BY LOCATION DESC) AS RANK_WP,
DENSE_RANK() OVER(ORDER BY ID) AS DENSE_RANK_WOP,
DENSE_RANK() OVER(PARTITION BY ID ORDER BY LOCATION DESC) AS DENSE_RANK_WP
FROM TSQL_16_RANK_FUNCTION











NOTE: WP -> With Partition, WOP -> Without partition

SELECT *,
ROW_NUMBER() OVER(ORDER BY ID) AS ROW_NUMBER_WOP,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS ROW_NUMBER_WP,
RANK() OVER(PARTITION BY ID ORDER BY ID) AS RANK_WP
FROM TSQL_16_RANK_FUNCTION


No comments: