DEV Community

alokz diptim!
alokz diptim!

Posted on

How to remove duplicates records in SQL (ROW_NUMBER, Partition, CTE) Part 1

SQL

I will begin by explaining the ROW_NUMBER function in SQL, which is used to assign sequential values to rows in a table.
sequential

The crucial concept here is the sequential increment applied to rows within a table. It's important to note that this function can be applied to temporary tables, tables without traditional IDs, or even tables with unique identifiers (UIDs).

Crucial to note

Typically, ROW_NUMBER is a function that can be used without a parameter, so it can be declared as ROW_NUMBER().
Row

The next crucial keyword is "OVER," which is used in conjunction with functions in SQL. It is required here to determine how the sets of rows in the table will be arranged. In most cases, it specify how we want to arrange the table.

Over arrangements

Now, the OVER function requires two parameters:

OVER ([PARTITION BY value_expression] [order_by_clause])
Enter fullscreen mode Exit fullscreen mode

However, our focus will be on the ORDER BY clause, with a mention of the PARTITION BY clause later.

Image description

In tables like this, the IDs are sequential, so we may not necessarily need the "row_number" for simple operations such as ordering the rows.
true

It can be waived in such cases but is typically used in more advanced scenarios.

Image description

However, tables with unique IDs like this require a Row_Number because they are not sequential. I can't even count them correctly if there are more than 2000 rows.
Cannot count

So, in order to perform further logic on this table, we may need to generate a Row_Number for it.

Our SQL table, which is named "Cars," has two columns: "Id" and "Name."

If we intend to fetch the rows, we would typically write:

SELECT * FROM Cars;
Enter fullscreen mode Exit fullscreen mode

However, if we want to include the RowNumber, you can use the ROW_NUMBER() function in your query. Here's an example:

SELECT 
Id, Name,
ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber
FROM Cars;
Enter fullscreen mode Exit fullscreen mode

This query will return the rows from the "Cars" table along with a RowNumber column that assigns a unique number to each row based on the "Id" column's order.

Image description

Now, this gets a bit more interesting

interesting

I will be posting the next series shortly.

Top comments (0)