SQL ROW_NUMBER and RANK – Use cases

Spread the love

ROW_NUMER()

SQL ROW_NUMBER() is used to number the result set. The return type is integers.

Syntax:

ROW_NUMBER() OVER (PARTITION BY col_name ORDER BY col_name)

EXAMPLE:

For example purpose we will use table named ‘EMPLOYEE’.

Lets use ROW_NUMBER() to number the rows.

SELECT *,
ROW_NUMBER() OVER(ORDER BY SALARY)
FROM EMPLOYEE;

You can see a new column is created and numbers have been given according to ‘SALARY’ . We used ORDER BY clause to order according to SALARY. Order is lowest to highest.

To change the order from lowest to hinghest use DESC keyword with ORDER BY clause.

SELECT *,
ROW_NUMBER() OVER(ORDER BY SALARY DESC)
FROM EMPLOYEE;

Now you can see the highest salary is number 1 because we used DESC keyword.

Lets use PARTITION BY clause with ROW_NUMBER()

SELECT *,
ROW_NUMBER() OVER(PARTITION BY DEPT_NAME ORDER BY SALARY)
FROM EMPLOYEE;

Now you can see all partition have their own numbering.

RANK()

SQL RANK() is used to also number the result set just like ROW_NUMBER but if two values are the same it repeats the number and skips the next number. The return type is integers.

Syntax:

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

EXAMPLE:

We are going to use the same demo table as above

SELECT *,
RANK() OVER(ORDER BY SALARY DESC)
FROM EMPLOYEE;

Now, we will use RANK() with PARTITION BY Clause

SELECT *,
RANK() OVER(PARTITION BY DEPT_NAME ORDER BY SALARY DESC)
FROM EMPLOYEE;

Learn More – SQL Window Functions

SOURCE: Microsoft T-SQL Documentation.

Thanks For Reading!