How to Use ROW_NUMBER OVER() in SQL

Question

Struggling to understand the implementation of ROW_NUMBER() OVER() in SQL? Can someone help me understand how to efficiently assign row numbers to query results?

Can I use count() to get the total number of rows?

Answer 1

ROW_NUMBER() is a function that assigns a unique row number to each row within a partition of a result set.

Say you have a table of Employees with columns FirstName and LastName and Salary. ROW_NUMBER() is used to assign a unique row number to each row in the result set. The OVER clause determines how the row numbers are assigned. In this case, it’s ordering by Salary in descending order. So, the employee with the highest salary will have a row number of 1, the next highest will have a row number of 2, and so on.

SELECT 
    FirstName, 
    LastName, 
    Salary,
    ROW_NUMBER() OVER(ORDER BY Salary DESC) AS RowNumber 
FROM 
    Employees

This is a sample table output for the same example

  FirstName    LastName   Salary    RowNumber
  John         Doe        80000     1
  Jane         Smith      75000     2
  Mike         Johnson    70000     3
  Emily        Davis      65000     4