[RESOLVED] Query help

I am using sql server 2000.

I have a query like this to get 7 records:

Code:

---

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.

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

Quote:

---

Originally Posted by riteshjain1982

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.

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

Code:

---

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

Code:

---

TRUNCATE TABLE xPTS

---

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

Quote:

---

Originally Posted by 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

Code:

---

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

Code:

---

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.

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 ??

Quote:

---

Originally Posted by 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 ??

---

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.

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

Code:

---

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 :)

Thank you, it worked great!

Quote:

---

Originally Posted by lucia

Thank you, it worked great!

---

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

Quote:

---

Originally Posted by riteshjain1982

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

---

I appreciated your time and help.