散分！保留前10条记录的SQL语句

[DawnPine]

用VB ADO 操作本地Access表
对某个字段做排序
然后保留前10条记录
删除其余记录

语句简洁者优先拿高分 :-)

[饮水需思源]

delete from tablename where id not in (select top 10 id from tablename order by id)

[cindytsai]

来学习的

[daisy8675]

用pubs中间的jobs表做实验。

Select top 10 Job_ID from jobs order by Job_ID
delete from jobs where Job_ID not in(Select top 10 Job_ID from jobs)

[daisy8675]

晕，刚看见左手挺快的

[maskdata]

delete tablename where id not in (select top 10 id from tablename order by id)

这分应该给我,我比左手少一个from哦,比他的简洁一点,哈哈

[DawnPine]

问题是ID不一定唯一
就是说第10个和第11个的ID可能重复

另:逆序时怎么处理?

[ljhdi]

来蹭分的

[饮水需思源]

'降序:
delete from tablename where id not in (select top 10 id from tablename order by id desc)

[DawnPine]

基本上也算解决了
等待更完美的答案
明天上午10点前结帖

另:左手应该改名叫快手,从我发这个帖到他看到、解答、回帖，仅8分钟

[fishmans]

在ACCESS中用delete 语句就是要加from 的，分还是给左手算了，ID不唯一就得找到一个唯一的标志，没有也要创建一个，如果这样那语句肯定短不了

[fishmans]

select top 10 * into temptable from table1 order by id (desc)
delete from table1
insert into table1 select * from temptable

[射天狼]

左手练习熟练了比右手还快，蹭分~~~~~`

[linger505]

楼上都说的很好了

来
蹭分~~~~~`

[DawnPine]

谢谢各位的解答与帮顶
结帖

:-)