SQL Delete all records only one not.... 2

[meny21]

Hi Guys!

Does anyone knows how I can make a delete of this kind?

For example, I have 3 records like this:

NAME VERSION
AA 3
AA 2
AA 4

I need only the last version of this group, example:

NAME VERSION
AA 4

But the thing here is, that I need to delete the others records from my original Table....

How can I make a Delete T-SQL that delete all the Older Versions of every group I have??? I just one to keep the new versions always in this table

Any ideas?

Thanks for any suggestions

MenyR

 

[AlexCuse]

Something like this?

delete from table
left join
(
select name, max(version) 
from table
group by name
) a
on table.name = a.name
and table.version = a.version
where a.version is null

Just test on a backup, and replace 'table' in the query with your table name.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[meny21]

Hey Alex looks that is something like you said....

just it makes me an error:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'left'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'a'.

But I think the idea is great..

Do you know what is missing?

 

[AlexCuse]

Ooh I forgot one part... Here goes:

delete [COLOR=blue][b]table[/b][/color] from table
left join
(
select name, max(version)
from table
group by name
) a
on table.name = a.name
and table.version = a.version
where a.version is null

This should get it done. I am a dope because I tested it, realized I forgot table, but forgot to post the new one! I was in a rush to get out of work.

Hope this one helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[meny21]

Hi Alex,

I get an error, it is in T-SQL? I tried in enterprise Manager for SQL 2000 like you said....

 

[gmmastros]

Alex is using a Max function but not giving it an alias.

delete table from table
left join
(
select name, max(version) [!]As Version[/!]
from table
group by name
) a
on table.name = a.name
and table.version = a.version
where a.version is null

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Oh man, I missed changing that one when I tested it too :-(

Sorry meny, hope it works now! Gracias George!

Ignorance of certain subjects is a great part of wisdom

 

[meny21]

Hey Guys,

You are really good this is what I needed..

Thanks a lot !!!

MenyR