This topic has been archived. It cannot be replied.
-
工作学习 / 专业技术讨论 / I have a SQL related question, couldn't find an good answer, anyone could help? Many thanks.
"How to delete the records in a table except for the top 1?"
-atomy(浮尘);
2007-6-8
(#3729563@0)
-
I don't have a good answer for you. but this might work: DELETE FROM test_table WHERE id<>(SELECT TOP 1 id from test_table)
-canadiantire(轮胎-ten.prahsc);
2007-6-8
(#3730100@0)
-
Thanks for the reply, actually the case is a bit complicated, the simple structure is "UserID, Description, ReceivedDate", there would be 1000 users in this table, in order to purge the table, only need to keep latest record inputed for each user.Currently I did this feature by going through user one by one, kind of stupid but works. I'm trying to look for a better solution.
-atomy(浮尘);
2007-6-8
{131}
(#3730216@0)
-
I'm not sure if I understood it correctly...why don't you update record instead of inserting... Overall, I feel the idea to delete all except for the top 1 is kinda weird.
-fatbean(热心人才只穿裤);
2007-6-8
(#3730532@0)
-
Thanks for the reply, this is the weired requirement from customer, they want to remove all the records in their table except for the latest one at the end of each year.
-atomy(浮尘);
2007-6-8
(#3730608@0)
-
Then is there any other more reliable way to identify this row instead of getting the top 1?
-fatbean(热心人才只穿裤);
2007-6-8
(#3730669@0)
-
no other column I can use other than these. Initially I want to add one more column, but not allowed. Thanks.
-atomy(浮尘);
2007-6-8
(#3730714@0)
-
this one may workDELETE FROM test
FROM test LEFT OUTER JOIN
(SELECT UserID, MAX(ReceivedDate) AS LastDate
FROM test AS test_1
GROUP BY UserID) AS derivedtbl_1 ON test.UserID = derivedtbl_1.UserID
WHERE (test.ReceivedDate <> derivedtbl_1.LastDate)
-vinatca(Enjoy today!);
2007-6-8
{341}
(#3730568@0)
-
thanks vinatca, I'll try it.
-atomy(浮尘);
2007-6-8
(#3730610@0)
-
Excellent vinatca, it works, it's still a bit slow when purging the table, but much much faster than what I did before. Many thanks, have a great weekend.
-atomy(浮尘);
2007-6-8
(#3730682@0)
-
how about this: add a new column [flag], when the action is required, copy the "top 1" and set the column a flag, delete all other records without the column flag, update the flag again. it should be faster
-sowen(sowen);
2007-6-8
(#3730690@0)
-
Hi It might be faster I think, but I'm not allowed to change the database structure at the moment. Thanks.
-atomy(浮尘);
2007-6-8
(#3730697@0)