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!
