1. Registered User
Join Date
Feb 2004
Location
Poland
Posts
32

Have table with two cols:

pkg int,
eqp int

with ex. values:

Code:
```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.

2. Just DBA
Join Date
Jul 2002
Location
Village, MD
Posts
621
Logic is not clear - could you add some details?

3. Registered User
Join Date
Feb 2004
Location
Poland
Posts
32
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?

4. The SQL Apostle
Join Date
Jul 2003
Location
The Dark Planet
Posts
1,401
Code:
```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

5. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322

6. Registered User
Join Date
Feb 2004
Location
Poland
Posts
32
Originally posted by Brett Kaiser
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).

7. Just DBA
Join Date
Jul 2002
Location
Village, MD
Posts
621
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?

8. Registered User
Join Date
Feb 2004
Location
Poland
Posts
32
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.

9. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Sledge hammer anyone?

Code:
```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```

10. Registered User
Join Date
Feb 2004
Location
Poland
Posts
32
Thanks Brett I'll check it at once.

11. Just DBA
Join Date
Jul 2002
Location
Village, MD
Posts
621
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.

12. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
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....

13. Registered User
Join Date
Feb 2004
Location
Poland
Posts
32
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

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•