December 24, 2012 at 2:29 pm
Hi,
I have a table with syn_trans with below cols:
(
Id integer(10),
Name varchar2(100),
seq_no integer(10),
seq_name varchar2(100),
gp_name varchar2(100)
)
There is no unique/primary key on the table.
A row exists in table with below values:
INSERT INTO syn_trans VALUES(60175,'AccountOverview',0,'Signon','StepGp');
Two days later I again execute the same insert statement and now there exists two records for the ID=60175
I want to retrieve the entry with latest timestamp or in other words I want to delete the entry for this ID which I added second time.
Plz provide the SQL for this.
Thanks in advance.
December 24, 2012 at 3:35 pm
Nidhi G (12/24/2012)
Hi,I have a table with syn_trans with below cols:
(
Id integer(10),
Name varchar2(100),
seq_no integer(10),
seq_name varchar2(100),
gp_name varchar2(100)
)
There is no unique/primary key on the table.
A row exists in table with below values:
INSERT INTO syn_trans VALUES(60175,'AccountOverview',0,'Signon','StepGp');
Two days later I again execute the same insert statement and now there exists two records for the ID=60175
I want to retrieve the entry with latest timestamp or in other words I want to delete the entry for this ID which I added second time.
Plz provide the SQL for this.
Thanks in advance.
First, there is nothing in the table structure to tell which record is inserted first. Second, if they are completely identical, does it really matter which of the duplicate records gets deleted?
December 24, 2012 at 3:57 pm
How do I delete one entry then?
December 26, 2012 at 12:00 am
Nidhi G (12/24/2012)
How do I delete one entry then?
this query will help you to remove duplicates
delete t from
(
select ROW_NUMBER() over (partition by Id order by Id ) as rank1,* from syn_trans) t
where t.rank1 <> 1
THOUGH it doesnt guarantee about the newest or oldest record deletion because you are not maintainig that OR you can add one another column like created_date (timestamp) or any identity column to maintain the sequence.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply