SQL在没有主ID的情况下获取表中的最后一行 [英] SQL get last rows in table WITHOUT primary ID

问题描述

我有一个表，其中有80万个条目没有主键.我不允许添加主键，并且我无法按 TOP 1 .... ORDER BY DESC进行排序，因为完成此任务需要花费几个小时.所以我尝试了以下方法:

I have a table with 800,000 entries without a primary key. I am not allowed to add a primary key and I cant sort by TOP 1 ....ORDER BY DESC because it takes hours to complete this task. So I tried this work around:

DECLARE @ROWCOUNT int, @OFFSET int
SELECT @ROWCOUNT = (SELECT COUNT(field) FROM TABLE)
SET @OFFSET = @ROWCOUNT-1


select TOP 1  FROM TABLE WHERE=?????NO PRIMARY KEY??? BETWEEN @Offset AND @ROWCOUNT

这当然是行不通的.

是否愿意使用此代码或更好的代码来检索表中的最后一行?

Anyway to do use this code/or better code to retrieve the last row in table?

推荐答案

我假设当您说最后几行"时，是指最后创建的行".

I assume that when you are saying 'last rows', you mean 'last created rows'.

即使您具有主键，使用它来确定行的创建顺序仍然不是最佳选择. 无法保证主键值较大的行是在主键值较小的行之后创建的.
即使主键位于标识"列上，您仍然可以始终使用以下命令覆盖插入时的标识值: set identity_insert on.

Even if you had primary key, it would still be not the best option to use it do determine rows creation order. There is no guarantee that that the row with the bigger primary key value was created after the row with a smaller primary key value.
Even if primary key is on identity column, you can still always override identity values on insert by using set identity_insert on.

最好有一个timestamp列，例如具有默认约束的CreatedDateTime. 您将在该字段上建立索引.
然后您的查询将简单，高效且正确:

It is a better idea to have timestamp column, for example CreatedDateTime with a default constraint. You would have index on this field.
Then your query would be simple, efficient and correct:

select top 1 *
from MyTable
order by CreatedDateTime desc

如果没有时间戳列，则无法确定最后一行".

If you don't have timestamp column, you can't determine 'last rows'.

这篇关于SQL在没有主ID的情况下获取表中的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！