Delete problem

Have table with two cols:

pkg int,
eqp int

with ex. values:

pkg eqp
1 1
1 1
2 3
2 3
2 3
3 1
4 1
4 1
4 1

Need to delete some records, so at end eqp column says how many records with the same pkg - from example above need to delete one record with pkg 1 and two with pkg 4. Want to do this without cursor. Any help appreciated.Logic is not clear - could you add some details?|||Ok - back to example:

we have two records with pkg = 1 (for equal pkg, eqp will be also equal), both of them have eqp = 1 - which means I need only one of those records - other one should be deleted. There are also 3 records with pkg = 3, they have eqp = 3 - which means it's ok (3 records, eqp = 3). For pkg = 3 it's also ok., but for pkg = 4, eqp = 1 (so only one record with pkg = 4 should stay, other 2 should be deleted).

Does it make clear?|||use pubs
go
create table mytable99(pkg int,eqp int)
GO
insert into mytable99
select 1,1
UNION ALL
select 1,1
UNION ALL
select 2,3
UNION ALL
select 2,3
UNION ALL
select 2,3
UNION ALL
select 3,2
UNION ALL
select 3,2
UNION ALL
select 3,2
UNION ALL
select 3,2
UNION ALL
select 4,1
UNION ALL
select 4,1
UNION ALL
select 4,1
UNION ALL
select 4,1
GO
SELECT * FROM MYTABLE99
GO
DROP TABLE MYTABLE99
GO

Current resultset
pkg eqp
---- ----
1 1
1 1
2 3
2 3
2 3
3 2
3 2
3 2
3 2
4 1
4 1
4 1
4 1

Needed resultset
pkg eqp
---- ----
1 1
2 3
2 3
2 3
3 2
3 2
4 1

Let the deletes begin :)

I am working on it ... this is for help of other guys|||What about package 2?|||Originally posted by Brett Kaiser
What about package 2?

Both in my and Enigma examples no deletion is needed. For pkg = 2 eqp = 3, so max 3 records with pkg = 2 allowed (as it is).|||Originally posted by MST78
Both in my and Enigma examples no deletion is needed. For pkg = 2 eqp = 3, so max 3 records with pkg = 2 allowed (as it is).

Do you have id column for this table?|||Originally posted by snail
Do you have id column for this table?

Nope. Addition of ID column is possible - but for some reasons I'd like to avoid that. With ID it wouldn't be such problem for me.|||Sledge hammer anyone?

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE TABLE myTable99(pkg int,eqp int)
GO

INSERT INTO mytable99(pkg, eqp)
SELECT 1,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 2,3 UNION ALL
SELECT 2,3 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 4,1 UNION ALL
SELECT 4,1 UNION ALL
SELECT 4,1 UNION ALL
SELECT 4,1
GO

SELECT * FROM myTable99
GO

DECLARE @.MIN_pkg int, @.MAX_pkg int, @.eqp int, @.sql varchar(8000)

CREATE TABLE #myTemp99(pkg int,eqp int)

SELECT @.MIN_pkg = MIN(pkg),@.MAX_pkg = MAX(pkg)
FROM myTable99

WHILE @.MIN_pkg <> @.MAX_pkg
BEGIN
SELECT TOP 1 @.eqp = eqp FROM myTable99 WHERE pkg = @.MAX_pkg

SELECT @.SQL = 'INSERT INTO #myTemp99(pkg, eqp)'
+ ' SELECT TOP ' + CONVERT(varchar(3),@.eqp)
+ 'pkg, eqp FROM myTable99 WHERE pkg = ' + CONVERT(varchar(3),@.MAX_pkg)

EXEC(@.SQL)

SELECT @.MAX_pkg = MAX([pkg])
FROM myTable99
WHERE [pkg] < @.MAX_pkg

END

SELECT TOP 1 @.eqp = eqp FROM myTable99 WHERE pkg = @.MIN_pkg

SELECT @.SQL = 'INSERT INTO #myTemp99(pkg, eqp)'
+ ' SELECT TOP ' + CONVERT(varchar(3),@.eqp)
+ 'pkg, eqp FROM myTable99 WHERE pkg = ' + CONVERT(varchar(3),@.MIN_pkg)

EXEC(@.SQL)

SELECT * FROM #myTemp99
GO

DROP TABLE #myTemp99
DROP TABLE myTable99
GO

SET NOCOUNT OFF
GO|||Thanks Brett :) I'll check it at once.|||Originally posted by MST78
Nope. Addition of ID column is possible - but for some reasons I'd like to avoid that. With ID it wouldn't be such problem for me.

You may try to redesign your table (or tables) because in future you'll have much more problems than this one. What Brett did just confirms this.|||Originally posted by snail
You may try to redesign your table (or tables) because in future you'll have much more problems than this one. What Brett did just confirms this.

I agree 100%.

And what I gave you is totally arbitrary.

It assumes that all your data is alike based on the package

I just thought it was a neat exercise...

god what a geek...|||I have no possibility to redesign this table (since it's not under my care) - I may only ask to add an ID column. I know it's ill-designed.

Thx for all :)