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

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

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).

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

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.

Now, the OVER function requires two parameters:

OVER ([PARTITION BY value_expression] [order_by_clause])

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

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.

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

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.

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;

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;

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.

Now, this gets a bit more interesting

I will be posting the next series shortly.