I know just enough about this to be dangerous...so I have a basic question.

One of the posters (GSquared) offered this method:

select 1;

while @@rowcount > 0

delete top (1000)

from table

where x= y;

Is...

Disregard this question... I think it may have been my script 'feeder' program.

Thanks everyone.

I used benyos' reply to work out a solution and was testing it when drew.allen responded. My query looks like drew.allen's and appears to work.

The part that I...

OK - Here's the question once again with, hopefully, a format that follows the forum etiquette.

The goal is to update the destination table with only the most recent record in...

Thanks, that works. I'll need to read up on OPENXML

That does it!

I was trying something similar but couldn't quite get it. I think I was using a subquery after the inner join...

Thanks.

Table and data:

CREATE TABLE [dbo].[tblArchiveRecords](

[SequenceID] [bigint] IDENTITY(-9223372036854775808,1) NOT NULL,

[ArchiveID] [int] NOT NULL

)

CREATE TABLE [dbo].[tblPublishPosition](

[SequenceID] [int] IDENTITY(1,1) NOT NULL,

[ArchiveID] [int] NOT NULL,

[PublishedPosition] [bigint] NOT NULL

)

insert into [dbo].[tblArchiveRecords] (ArchiveID) values (1)

insert into...

I changed the query to

DELETE MyTable FROM

(SELECT IdentityColumn,DENSE_RANK() OVER(PARTITION BY ObjectId,DataKey ORDER BY IdentityColumn DESC) 'Rank'

FROM MyTable) MyTable1

WHERE MyTable.IdentityColumn = MyTable1.IdentityColumn

AND MyTable1.Rank > 1

and that appears to...

Thanks Pandian,

I tried that query...it appears that it doesn't leave the record with the greatest identity value and remove the rest. I think the lowest identity value was the one...

OK, I think I've got it.

In my test to show the records that would be deleted, I ran only the select subquery:

select * from MyTable t

where t.ObjectId=MyTable.ObjectId

and t.DataKey=MyTable.DataKey

and t.IdentCol>MyTable.IdentCol

I didn't...

Thanks Mark,

I'm a newbie with SQL, so forgive the ignorance. I don't really understand why that query works. I ran just the 'select' portion to see if it returned...