Posts Tagged ‘DENSE_RANK’

I have a dataset as shown below. When the OP_CODE is null and PRIORITY = 1, I need to shift the OP_CODE of the Priority = 2 to become 1 and 3 to become 2 and 4 to become 3.

The dataset also has the history of the changes made to the records.

At first I ranked the records by using the RANK function but it didn’t work at all. It set the ranking incorrectly as shown below. It didn’t work because it ranks every record instead of every distinct record in the partition.

I then used DENSE_RANK function as it ranks the records within the partition of a result set, without any gaps in the ranking. When there is a tie in the partition, it ranks it to the same number. In this example, there are 3 records with the Priority_Shifted of 30000. These 3 records should all be ranked to 4. The DENSE_RANK function has done it! 🙂



Read Full Post »

%d bloggers like this: