Deleting rows less than max value of certain Code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Deleting rows less than max value of certain Code

Author

Topic

hendrasd
Starting Member

20 Posts

Posted - 2003-08-13 : 00:31:04

Hi guys,

I have a table TTemporary with contents like this :
Code Value
A1 3
A2 4
A3 5
A2 5
A3 3

I want to delete rows on the table which has a value less than the maximum value for a certain Code. The result of the query would be like this :
Code Value
A1 3
A2 5
A3 5

I've thought a solution, but it is using Cursor method.
Is there any way to do it without using Cursor ?

Thanks in advance ...

-- Never stop learning --

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-13 : 00:44:15

Delete all rows of a code that're less than the max value?

Here's the rows of max values

SELECT Code, MAX(Value) as MaxValue FROM TTemporary GROUP BY Code

Here's a Select that'll select all the other rows

SELECT *

FROM TTemporary T

INNER JOIN (

SELECT Code, MAX(Value) as MaxValue FROM TTemporary GROUP BY Code

) A ON A.Code = T.Code

WHERE A.MaxValue <> T.Value

Now to delete those rows

DELETE T

FROM TTemporary T

INNER JOIN (

SELECT Code, MAX(Value) as MaxValue FROM TTemporary GROUP BY Code

) A ON A.Code = T.Code

WHERE A.MaxValue <> T.Value

I haven't tried this. No warranty expressed or implied.

Sam

ps. pretty sure this doesn't work. It's an UPDATE syntax I used on the DELETE.

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-13 : 00:48:03

(Flailing sounds)Let's try this again

DELETE

FROM TTemporary T

INNER JOIN (

SELECT Code, MAX(Value) as MaxValue FROM TTemporary GROUP BY Code

) A ON A.Code = T.Code

WHERE A.MaxValue <> T.Value

hendrasd
Starting Member

20 Posts

Posted - 2003-08-13 : 00:57:12

Yipiiiiii thanxxx ... >-(^_^)-<

-- Never stop learning --

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources