Update and leave latest item

[JezEling]

Hi All,

I have a table which contains a list of item numbers and revisions. I have been asked to remove all but the latest revisions from this table, I have tried to achieve this using a CASE query but to now avail (CASE is not something i've really used before ). Does anyone have any suggestions has to how I can achieve this?

Many Thanks In Advance

Jez

 

[Welshbird]

Can you post us some example data so we can see what we're dealing with?

And a table struction too if possible.

Fee.

"The question should be, is it worth trying to do, not can it be done"

 

[JezEling]

Table structure

PartNumber | Revision

Sample Data

123.123 | 1
123.123 | 2
123.123 | 3

Where the latest revision would be 3, so revisions 1 and 2 need to be deleted.

Jez

 

[seabra]

You can try something like this

delete from tablename where revision not in (select top 1 revision from tablename order by revision desc)

MS

 

[Welshbird]

I'm not sure that works to be honest

Ok then, this is my attempt;

create table JezEling
(
partnumber varchar(15),
revision int
)

and the data looks like this

partnumber      revision    
--------------- ----------- 
123.123         1
123.123         2
123.123         3
124.124         1
124.124         2
124.124         3
124.124         4

I'd be tempted to add a column to mark for deletion so you can see it before you actually delete it, in whch case

alter table jezeling
add ForDelete varchar(1)

then create a view to link back to for the update

create view temp_view as
select
partnumber, max(revision)as Rev from jezeling
group by partnumber
go
update jezeling
set ForDelete = 'Y'
where 
( CONVERT(varchar(50), partnumber) + convert(varchar(50),revision)) not in
(select CONVERT(varchar(50), partnumber) + CONVERT(varchar(50),rev ) from temp_view)
go
select * from jezeling

This means you could then just do

delete from jezeling where fordelete = 'Y'

Hope this helps.



Fee.

"The question should be, is it worth trying to do, not can it be done"