SQL-Could you help me writing a SQL query which remakes new table from existing table without duplicated ID?

Could you help me write a SQL query which remakes a new table from existing table without duplicated ID?

I want to store most new data among all duplicated Id.

The table looks like this:

My table (actually tables) is at Amazon Redshift DB. And while I'm storing my data there.

I found out same Id is getting duplicated regardless of primary key.

So I've decided to recreate the table without duplicated data (erasing seems costly).

From the example table, the new table I want would be like this.

Preserving latest data of 'xd01s' and getting rid of other old 'xd01s'.

Any of columns doesn't tell what is most recent one (there isn't time nor date..nor any incremented value).

I think the biggest rownumber from initial order is only way to notice most recent one. But my SQL query I've tried keeps failing (lack of experience..).

So far I've used this query with psycopg2 python package.

alter table my_table rename to my_table_old

create table my_table 
as 
    select distinct * 
    from my_table_old

drop table if exists my_table_old cascade

but this only gets rid of data which has all column values are same.

Great thanks for reading my question and answering it ^^.

This query will do:

SELECT
	Id
	,name
	,visited
	,column1
	,column2
FROM	(
		SELECT
			Id
			,name
			,visited
			,column1
			,column2
			,ROW_NUMBER() OVER (PARTITION BY Id)	AS rn
		FROM
			my_table_old
		)	AS my_table_aux
WHERE
	rn = 1
;

With the subquery we are creating a new column that assigns a sequential integer to each repetition of the same value within the field Id.

The outer query filters the inner result to keep just the first of the rows within a group rows with the same Id (as we are sure that there will be at least one record for each Id with a rn value of 1).

Note that nothing guaranties that the result will keep the original order of the records. We would need a field to provide an ORDER BY clause for the OVER clause.

Let's suppose you have a datetime field named updated_on that can be used to sort the records from newest to oldest. In that case, the query should be as follows:

SELECT
	Id
	,name
	,visited
	,column1
	,column2
	,updated_on
FROM	(
		SELECT
			Id
			,name
			,visited
			,column1
			,column2
			,updated_on
			,ROW_NUMBER() OVER (PARTITION BY Id ORDER BY updated_on DESC)	AS rn
		FROM
			my_table_old
		)	AS my_table_aux
WHERE
	rn = 1
;

Here you can read the details of ROW_NUMBER window function.