Use ROW_NUMBER to Create a Unique ID

Learn how to use the ROW_NUMBER() windows function to generate a unique ID.

Problem:

You have a table named Teams with information about various sports teams, including their names, points, and conferences. You want to add a unique identifier to each row in the table to track them easily, but you don’t have a unique column to serve as an ID. However, you can use the ROW_NUMBER() window function in SQL to generate a unique column ID based on a specific ordering.

Note: You can find the Team table in the Wincode database.

Background:

The ROW_NUMBER() is a powerful window function in SQL that assigns a unique integer value to each row within a partition of a result set.

The function allows you to define the order of the rows using an ORDER BY clause within the OVER() function. This can be particularly useful when you need to create a unique identifier for each row within a table.

Solution:

To create a TeamID for the Teams table using the ROW_NUMBER() window function, follow these steps:

• Define the order in which you want to assign the IDs. For this example, let’s order the teams by their points in descending order.
• Use the ROW_NUMBER() function along with the OVER() clause to generate the unique IDs. The OVER() clause defines the partitioning and ordering of the rows.

ROW_NUMBER() SQL Query

Here is the query you can use to generate the TeamID:

select row_number() over(order by points desc) TeamID,
    Team, Points, Conference
from Teams;

And the results it produces:

Explanation or ROW_NUMBER:

In the above SQL query, the row_number() window function generates a sequence of integers.  The function assigns the points in order of team points as defined by the order by clause.

Conclusion:

Using the ROW_NUMBER() window function, we successfully added a unique TeamID to the Teams table based on the ordering of the points. This new column can serve as a primary key or a unique identifier, making it easier to reference and track individual teams within the dataset.

The versatility of window functions like ROW_NUMBER() enables SQL developers to efficiently manipulate data and solve various problems in creative and effective ways.