[RESOLVED] Query help

[lucia]

I am using sql server 2000.

I have a query like this to get 7 records:

select max(id),db_num from xPTS group by db_num

I need run another query to delete all records in table XPTS except this 7 records.

Can anyone help? Thanks.

[riteshjain1982]

it's all depend upon whether your table is indexed or not and also depend upon no. of rows in table.

[lucia]

it's all depend upon whether your table is indexed or not and also depend upon no. of rows in table.

Thanks. It is not indexexd and there are 1.5 m rows in table.

[riteshjain1982]

i would say try putting PK constaint on column id (which i assume must be a unique/record) and if you alredy know these 7 records ID in advanced try to put it like

delete from xPTS 
WHERE id not in (1,2,3,4,5,6,7)

also if possible for you to re-enter this 7 rows than simply use

TRUNCATE TABLE xPTS

and re-add above 7 rows,this will be much more faster.

[lucia]

i would say try putting PK constaint on column id (which i assume must be a unique/record) and if you alredy know these 7 records ID in advanced try to put it like

delete from xPTS 
WHERE id not in (1,2,3,4,5,6,7)

also if possible for you to re-enter this 7 rows than simply use

TRUNCATE TABLE xPTS

and re-add above 7 rows,this will be much more faster.

No, the ID is not a PK and the 7 rows changes everytime I run the query.

[riteshjain1982]

May be you can try this

1>move these 7 rows to some Temp. table
2>Call TRUNCATE TABLE xPTS
3>Re-Move the Data from Temp Table to xPTS
4>DROP TempTable

No, the ID is not a PK and the 7 rows changes everytime I run the query.
wondering each time you 1.5 million rows ??

[lucia]

May be you can try this

1>move these 7 rows to some Temp. table
2>Call TRUNCATE TABLE xPTS
3>Re-Move the Data from Temp Table to xPTS
4>DROP TempTable

No, the ID is not a PK and the 7 rows changes everytime I run the query.
wondering each time you 1.5 million rows ??

Yes,put them in temp table is a way. And also, I can use array to hold these Ids and then use them in 'delete' query. I just wonder if there is a better way--one query can solve all.

Thank you.

[riteshjain1982]

ok so here is your query,Hope this is what you are looking for.

DELETE FROM xPTS
WHERE id NOT IN
(select max(id) from xPTS group by db_num)

EDIT: Sorry i overlook I need run another query to delete all records in table XPTS except this 7 records. In your first post

[lucia]

Thank you, it worked great!

[riteshjain1982]

Thank you, it worked great!

and sorry for all those post just becs. i missed last line of post #1

[lucia]

and sorry for all those post just becs. i missed last line of post #1

I appreciated your time and help.