如何从 SQL Server 中的表中获取最后一行? [英] How to get the last row from a table in SQL Server?

问题描述

我有一张表，它只有一列 varchar.

我想获取最后一行，但我无法应用order by.

以下查询没有给出所需的结果.

按 columnName desc 从 SampleTbl order 中选择 top 1

解决方案

数据库表本质上是未排序的.表格中没有最后一行.

如果有聚集索引，则记录的存储顺序与聚集索引相同，但这并不意味着这是行的顺序 - 除非您在中指定 order by 子句您的查询，没有数据库保证所述查询返回的行的顺序.

来自关于表格的维基百科页面(重点是我的):<块引用>

就数据库的关系模型而言，表可以被认为是一种关系的方便表示，但两者并不严格等价.例如，SQL 表可能包含重复的行，而真正的关系不能包含重复的元组.类似地，作为表的表示意味着对行和列的特定排序，而关系是明确无序的.但是，除非在查询表的 SELECT 语句中指定了 ORDER BY 子句，否则数据库系统不保证行的任何排序.

另见 Aaron Betrand 的 Bad要踢的习惯:依赖无证行为，标题为Ordering without an ORDER BY"的段落:

<块引用>

... 我想说得很清楚:除非您使用 ORDER BY 子句，否则排序是任意的.你永远不应该依赖你在没有 ORDER BY 的查询中观察到的排序——如果你真的不关心返回结果的顺序，你应该只发出没有 ORDER BY 子句的查询.在这种情况下，您不妨想象这些行每次都会以不同的随机顺序返回，即使情况并非如此(随机的含义与任意的完全不同，但就像我说的那样，想象一下).

I have one table it has only one column which is varchar.

I want to get the last row, but I cannot apply order by.

the following query does not give the required result.

select top 1 from SampleTbl order by columnName desc

解决方案

Database tables are unsorted by nature. There is no last row in the table.

If there is a clustered index, The records are stored in the same order as the clustered index, but that does not mean that's the order of the rows - Unless you specify an order by clause in your query, no database guarantees the order of the rows returned by said query.

From Wikipedia page on tables (emphasis mine):

In terms of the relational model of databases, a table can be considered a convenient representation of a relation, but the two are not strictly equivalent. For instance, an SQL table can potentially contain duplicate rows, whereas a true relation cannot contain duplicate tuples. Similarly, representation as a table implies a particular ordering to the rows and columns, whereas a relation is explicitly unordered. However, the database system does not guarantee any ordering of the rows unless an ORDER BY clause is specified in the SELECT statement that queries the table.

See also Aaron Betrand's Bad habits to kick : relying on undocumented behavior, the paragraph titled "Ordering without an ORDER BY":

... I want to make it quite clear: ordering is arbitrary unless you use an ORDER BY clause. You should never, ever, ever rely on the ordering you observe in a query without an ORDER BY -- and you should only issue a query without an ORDER BY clause if you truly do not care what order the results come back. In such a case, you may as well imagine that the rows are going to come back in a different, random order each time, even though that is not truly the case (random has a meaning completely separate from arbitrary, but like I said, just imagine).

这篇关于如何从 SQL Server 中的表中获取最后一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！