CODEWITHSUNDEEP
sqlvertica
PhysicsIslyfe
PhysicsIslyfe

Reputation: 11

(SQL Vertica) Create a new table dropping the rows with duplicates in a single column

I am currently trying to write a query on SQL vertica that will delete the first row in which there is a duplicate value in a specific single column. I am using SQL Vertica. Does anyone know how to do this? ** Update**

I have found that the duplicates are infact in the primary key but they have an associated timestamp. So the idea is that if a duplicate is located in the primary key, I want to remove the rows with the old timestamp. Does anyone know how I can do this?

Upvotes: 0

Views: 253

Answers (2)

Isuri Subasinghe
Isuri Subasinghe

Reputation: 147

What do you mean by 'duplicate is located in the primary key'?

It is not possible to have more than one row in the same table with the same primary key

Upvotes: 0

Indrakumara
Indrakumara

Reputation: 1645

Assume you have a primary column in the table

Create a test table and insert data 

CREATE TABLE test (
  id INT,
  exam_id INT
);
INSERT INTO test (id,exam_id) VALUES (1,1);
INSERT INTO test (id,exam_id) VALUES (2,1);
INSERT INTO test (id,exam_id) VALUES (3,2);
INSERT INTO test (id,exam_id) VALUES (4,2);

delete query 

delete from test where id in (

select id from (SELECT 
   id, 
  exam_id,
   ROW_NUMBER() OVER (
      PARTITION BY exam_id
      ORDER BY exam_id
   ) row_num
FROM 
   test
ORDER BY 
   id) x where row_num =1) ;

output 

  select * from test;

Upvotes: 1

Related Questions