如何在不排序的情况下选择表格中的最后5行？

I want to select the last 5 records from a table in SQL Server without arranging the table in ascending or descending order.

我想从SQL Server中的表中选择最后5条记录，而不按升序或降序排列表。

20 个解决方案

#1

This is just about the most bizarre query I've ever written, but I'm pretty sure it gets the "last 5" rows from a table without ordering:

这只是我写过的最奇怪的查询，但我很确定它从表中获得“最后5”行而没有排序：

select * 
from issues
where issueid not in (
    select top (
        (select count(*) from issues) - 5
    ) issueid
    from issues
)

Note that this makes use of SQL Server 2005's ability to pass a value into the "top" clause - it doesn't work on SQL Server 2000.

请注意，这利用了SQL Server 2005将值传递到“top”子句的能力 - 它在SQL Server 2000上不起作用。

#2

Suppose you have an index on id, this will be lightning fast:

假设你有一个关于id的索引，这将是闪电般的：

SELECT * FROM [MyTable] WHERE [id] > (SELECT MAX([id]) - 5 FROM [MyTable])

#3

You need to count number of rows inside table ( say we have 12 rows )
你需要计算表内的行数（比如我们有12行）
then subtract 5 rows from them ( we are now in 7 )
然后从它们中减去5行（我们现在在7行）
select * where index_column > 7

select * where index_column> 7
```
select * from users
where user_id > 
( (select COUNT(*) from users) - 5)
```
you can order them ASC or DESC

你可以订购它们ASC或DESC

But when using this code

但是在使用这段代码时
```
select TOP 5 from users order by user_id DESC
```
it will not be ordered easily.

它不会轻易订购。

#4

The way your question is phrased makes it sound like you think you have to physically resort the data in the table in order to get it back in the order you want. If so, this is not the case, the ORDER BY clause exists for this purpose. The physical order in which the records are stored remains unchanged when using ORDER BY. The records are sorted in memory (or in temporary disk space) before they are returned.

您的问题的措辞方式使得听起来您认为必须在表格中实际使用数据，以便按照您想要的顺序将其恢复。如果是这样，则不是这种情况，为此目的存在ORDER BY子句。使用ORDER BY时，存储记录的物理顺序保持不变。记录在返回之前在内存（或临时磁盘空间）中排序。

Note that the order that records get returned is not guaranteed without using an ORDER BY clause. So, while any of the the suggestions here may work, there is no reason to think they will continue to work, nor can you prove that they work in all cases with your current database. This is by design - I am assuming it is to give the database engine the freedom do as it will with the records in order to obtain best performance in the case where there is no explicit order specified.

请注意，如果不使用ORDER BY子句，则无法保证返回记录的顺序。因此，虽然这里的任何建议都可行，但没有理由认为它们会继续工作，也无法证明它们适用于当前数据库的所有情况。这是设计的 - 我假设它是为数据库引擎提供*的记录，以便在没有指定显式顺序的情况下获得最佳性能。

Assuming you wanted the last 5 records sorted by the field Name in ascending order, you could do something like this, which should work in either SQL 2000 or 2005:

假设您希望按字段名称升序排序的最后5条记录，您可以执行类似这样的操作，这应该适用于SQL 2000或2005：

select Name 
from (
    select top 5 Name 
    from MyTable 
    order by Name desc
) a 
order by Name asc

#5

Search 5 records from last records you can use this,

从你可以使用的最近记录中搜索5条记录，

SELECT *
FROM   Table Name
WHERE  ID <= IDENT_CURRENT('Table Name')
AND ID >= IDENT_CURRENT('Table Name') - 5

#6

Without an order, this is impossible. What defines the "bottom"? The following will select 5 rows according to how they are stored in the database.

没有订单，这是不可能的。是什么定义了“底部”？以下将根据它们在数据库中的存储方式选择5行。

SELECT TOP 5 * FROM [TableName]

SELECT TOP 5 * FROM [TableName]

#7

Well, the "last five rows" are actually the last five rows depending on your clustered index. Your clustered index, by definition, is the way that he rows are ordered. So you really can't get the "last five rows" without some order. You can, however, get the last five rows as it pertains to the clustered index.

好吧，“最后五行”实际上是最后五行，具体取决于您的聚集索引。根据定义，您的聚簇索引是按行排序的方式。所以如果没有一些订单，你真的无法获得“最后五行”。但是，您可以获得与聚簇索引相关的最后五行。

SELECT TOP 5 * FROM MyTable
ORDER BY MyCLusteredIndexColumn1, MyCLusteredIndexColumnq, ..., MyCLusteredIndexColumnN DESC

#8

If you know how many rows there will be in total you can use the ROW_NUMBER() function. Here's an examble from MSDN (http://msdn.microsoft.com/en-us/library/ms186734.aspx)

如果您知道总共有多少行，则可以使用ROW_NUMBER（）函数。这是来自MSDN的考试（http://msdn.microsoft.com/en-us/library/ms186734.aspx）

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

#9

select * from table limit 5 offset (select count(*) from table) - 5;

#10

Last 5 rows retrieve in mysql

最后5行在mysql中检索

This query working perfectly

此查询工作正常

SELECT * FROM (SELECT * FROM recharge ORDER BY sno DESC LIMIT 5)sub ORDER BY sno ASC

要么

select sno from(select sno from recharge order by sno desc limit 5) as t where t.sno order by t.sno asc

#11

In SQL Server 2012 you can do this :

在SQL Server 2012中，您可以这样做：

Declare @Count1 int ;

Select @Count1 = Count(*)
FROM    [Log] AS L

SELECT  
   *
FROM    [Log] AS L
ORDER BY L.id
OFFSET @Count - 5 ROWS
FETCH NEXT 5 ROWS ONLY;

#12

You can retrieve them from memory.
So first you get the rows in a DataSet, and then get the last 5 out of the DataSet.

您可以从内存中检索它们。首先，您获取DataSet中的行，然后获取DataSet中的最后5个。

#13

There is a handy trick that works in some databases for ordering in database order,

在一些数据库中有一个方便的技巧可用于按数据库顺序排序，

SELECT * FROM TableName ORDER BY true

Apparently, this can work in conjunction with any of the other suggestions posted here to leave the results in "order they came out of the database" order, which in some databases, is the order they were last modified in.

显然，这可以与此处发布的任何其他建议一起使用，以便将结果保留为“它们来自数据库的顺序”顺序，在某些数据库中，这是他们上次修改的顺序。

#14

select * 
from table 
order by empno(primary key) desc 
fetch first 5 rows only

#15

When number of rows in table is less than 5 the answers of Matt Hamilton and msuvajac is Incorrect. Because a TOP N rowcount value may not be negative.
A great example can be found Here.

当表中的行数小于5时，Matt Hamilton和msuvajac的答案不正确。因为TOP N行数值可能不是负数。在这里可以找到一个很好的例子。

#16

i am using this code:

我正在使用此代码：

select * from tweets where placeID = '$placeID' and id > (
    (select count(*) from tweets where placeID = '$placeID')-2)

#17

Try this, if you don't have a primary key or identical column:

如果您没有主键或相同列，请尝试此操作：

select [Stu_Id],[Student_Name] ,[City] ,[Registered], 
       RowNum = row_number() OVER (ORDER BY (SELECT 0))    
from student
ORDER BY RowNum desc

#18

DECLARE @MYVAR  NVARCHAR(100)
DECLARE @step  int
SET @step = 0;


DECLARE MYTESTCURSOR CURSOR
DYNAMIC 
FOR
SELECT col FROM [dbo].[table]
OPEN MYTESTCURSOR
FETCH LAST FROM MYTESTCURSOR INTO @MYVAR
print @MYVAR;


WHILE @step < 10
BEGIN   
    FETCH PRIOR FROM MYTESTCURSOR INTO @MYVAR
        print @MYVAR;
        SET @step = @step + 1;
END   
CLOSE MYTESTCURSOR
DEALLOCATE MYTESTCURSOR

#19

Thanks to @Apps Tawale , Based on his answer, here's a bit of another (my) version,

感谢@Apps Tawale，基于他的回答，这是另一个（我的）版本，

To select last 5 records without an identity column,

要选择没有标识列的最后5条记录，

select top 5 *, 
   RowNum = row_number() OVER (ORDER BY (SELECT 0))    
from  [dbo].[ViewEmployeeMaster]
ORDER BY RowNum desc

Nevertheless, it has an order by, but on RowNum :)

不过，它有一个订单，但在RowNum :)

Note(1): The above query will reverse the order of what we get when we run the main select query.

注意（1）：上面的查询将颠倒我们运行主选择查询时得到的顺序。

So to maintain the order, we can slightly go like:

所以为了维持秩序，我们可以稍微调整一下：

select *, RowNum2 = row_number() OVER (ORDER BY (SELECT 0))    
from ( 
        select top 5 *, RowNum = row_number() OVER (ORDER BY (SELECT 0))    
        from  [dbo].[ViewEmployeeMaster]
        ORDER BY RowNum desc
     ) as t1
order by RowNum2 desc

Note(2): Without an identity column, the query takes a bit of time in case of large data

注意（2）：如果没有标识列，则在大数据的情况下查询会花费一些时间

#20

-1

Get the count of that table

获取该表的计数

select count(*) from TABLE
select top count * from TABLE where 'primary key row' NOT IN (select top (count-5) 'primary key row' from TABLE)

#1