Analytical Functions in SQL. Part 2

Partition clause restricts an analytic function to within a partition boundary.

To illustrate, I use the below data set that contains columns — educational courses, category, quantity sold .

Problem Statement

Find the TOP 3 most sold product in each category.

1- Use RANK in tandem with PARTITION BY clause

2 - Use ROW_NUMBER in tandem with PARTITION BY clause

Using ROW_NUMBER eliminates the tie .

Likewise to find the top most selling product in each category, tweak the WHERE condition.

Apply RANK, DENSE_RANK and ROWNUM functions in tandem with PARTITION BY clause to rank data within partitions.