SQL Server选择最后的N行。

This is a known question but the best solution I've found is something like:

这是一个已知的问题，但我找到的最好的解决方法是:

SELECT TOP N *
FROM MyTable
ORDER BY Id DESC

I've a table with lots of rows. It is not a posibility to use that query because it takes lot of time. So how can I do to select last N rows without using ORDER BY?

我有一张有很多行的桌子。使用这个查询并不是一个正确的方法，因为它需要大量的时间。那么如何不使用ORDER BY来选择最后的N行呢?

EDIT

编辑

Sorry duplicated question of this one

抱歉，这个问题重复了。

15 个解决方案

#1

You can do it by using the ROW NUMBER BY PARTITION Feature also. A great example can be found here:

您也可以通过分区特性来使用行号。这里有一个很好的例子:

I am using the Orders table of the Northwind database... Now let us retrieve the Last 5 orders placed by Employee 5:

我正在使用Northwind数据库的Orders表…现在让我们来检索一下5号员工的最后5个订单:

SELECT ORDERID, CUSTOMERID, OrderDate
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS OrderedDate,*
    FROM Orders
) as ordlist

WHERE ordlist.EmployeeID = 5
AND ordlist.OrderedDate <= 5

#2

You can make SQL server to select last N rows using this SQL:

您可以让SQL server使用这个SQL选择最后的N行:

select * from tbl_name order by id desc limit N;

#3

I tested JonVD's code, but found it was very slow, 6s.

我测试了JonVD的代码，但是发现它非常慢，6s。

This code took 0s.

这段代码0。

SELECT TOP(5) ORDERID, CUSTOMERID, OrderDate    
FROM Orders where EmployeeID=5    
Order By OrderDate DESC

#4

If you want to select last numbers of rows from a table.

如果要从表中选择最后的行数。

Syntax will be like

语法就像

 select * from table_name except select top 
 (numbers of rows - how many rows you want)* from table_name

These statements work but differrent ways. thank you guys.

这些表述起作用，但有不同的方式。谢谢你们。

 select * from Products except select top (77-10) * from Products

in this way you can get last 10 rows but order will show descnding way

这样，您可以得到最后10行，但顺序将显示descnding方式。

select top 10 * from products
 order by productId desc 

---

 select * from products
 where productid in (select top 10 productID from products)
 order by productID desc

---

 select * from products where productID not in 
 (select top((select COUNT(*) from products ) -10 )productID from products)

#5

Is "Id" indexed? If not, that's an important thing to do (I suspect it is already indexed).

“Id”索引吗?如果不是，那是很重要的事情(我怀疑它已经被索引了)。

Also, do you need to return ALL columns? You may be able to get a substantial improvement in speed if you only actually need a smaller subset of columns which can be FULLY catered for by the index on the ID column - e.g. if you have a NONCLUSTERED index on the Id column, with no other fields included in the index, then it would have to do a lookup on the clustered index to actually get the rest of the columns to return and that could be making up a lot of the cost of the query. If it's a CLUSTERED index, or a NONCLUSTERED index that includes all the other fields you want to return in the query, then you should be fine.

另外，是否需要返回所有列?你可以得到实质性的改善速度如果你只需要一个较小的列的子集,可以完全满足ID列上的索引——例如,如果你有一个非聚集索引ID列,没有其他字段包含在索引,那么它将不得不做一个聚集索引查找,得到其他列返回,可以弥补很多查询的成本。如果它是一个聚集索引，或者是一个非聚集索引，它包含您想要在查询中返回的所有其他字段，那么您应该没问题。

#6

Here's something you can try without an order by but I think it requires that each row is unique. N is the number of rows you want, L is the number of rows in the table.

这里有一些东西你可以尝试，但我认为它要求每一行都是唯一的。N是你想要的行数，L是表中的行数。

select * from tbl_name except select top L-N * from tbl_name

As noted before, which rows are returned is undefined.

如前所述，返回的行是未定义的。

EDIT: this is actually dog slow. Of no value really.

编辑:这实际上是狗慢。没有价值的。

#7

select * from (select top 6 * from vwTable order by Hours desc) T order by Hours

#8

First you most get record count from

首先，你的记录数来自。

 Declare @TableRowsCount Int
 select @TableRowsCount= COUNT(*) from <Your_Table>

And then :

然后:

In SQL Server 2012

在SQL Server 2012

SELECT *
FROM  <Your_Table> As L
ORDER BY L.<your Field>
OFFSET <@TableRowsCount-@N> ROWS
FETCH NEXT @N ROWS ONLY;

In SQL Server 2008

在SQL Server 2008

SELECT *
FROM 
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS sequencenumber, *
FROM  <Your_Table>
    Order By <your Field>
) AS TempTable
WHERE sequencenumber > @TableRowsCount-@N 

#9

In a very general way and to support SQL server here is

这是一种非常普遍的方式，支持SQL server。

SELECT TOP(N) *
FROM tbl_name
ORDER BY tbl_id DESC

and for the performance, it is not bad (less than one second for more than 10,000 records On Server machine)

对于性能而言，它并不坏(在服务器机器上超过10,000个记录不到一秒)

#10

This may not be quite the right fit to the question, but…

这可能不太适合这个问题，但是……

OFFSET clause

The OFFSET number clause enables you to skip over a number of rows and then return rows after that.

OFFSET number子句允许您跳过一些行，然后在此之后返回行。

That doc link is to Postgres; I don't know if this applies to Sybase/MS SQL Server.

那个doc链接是Postgres;我不知道这是否适用于Sybase/MS SQL Server。

#11

This query returns last N rows in correct order, but it's performance is poor

这个查询以正确的顺序返回最后的N行，但是它的性能很差。

select *
from (
    select top N *
    from TableName t
    order by t.[Id] desc
) as temp
order by temp.[Id]

#12

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

#13

A technique I use to query the MOST RECENT rows in very large tables (100+ million or 1+ billion rows) is limiting the query to "reading" only the most recent "N" percentage of RECENT ROWS. This is real world applications, for example I do this for non-historic Recent Weather Data, or recent News feed searches or Recent GPS location data point data.

我使用一种技术来查询非常大的表中最近的行(100+ 100或10亿行)，这限制了查询只“读取”最近行的“N”百分比。这是真实的世界应用程序，例如我为非历史性的最近天气数据，或最近的新闻提要搜索或最近的GPS定位数据点数据。

This is a huge performance improvement if you know for certain that your rows are in the most recent TOP 5% of the table for example. Such that even if there are indexes on the Tables, it further limits the possibilites to only 5% of rows in tables which have 100+ million or 1+ billion rows. This is especially the case when Older Data will require Physical Disk reads and not only Logical In Memory reads.

这是一个巨大的性能改进，如果您知道您的行是在最近的前5%的表中。这样，即使表上有索引，它也会进一步限制在表中仅有5%行的可能性，而这些表中有1亿或10亿行。特别是当旧数据需要物理磁盘读取时，而不只是在内存中读取逻辑。

This is well more efficient than SELECT TOP | PERCENT | LIMIT as it does not select the rows, but merely limit the portion of the data to be searched.

这比选择TOP | % |限制要有效得多，因为它不选择行，而只是限制要搜索的数据的部分。

DECLARE @RowIdTableA BIGINT
DECLARE @RowIdTableB BIGINT
DECLARE @TopPercent FLOAT

-- Given that there is an Sequential Identity Column
-- Limit query to only rows in the most recent TOP 5% of rows
SET @TopPercent = .05
SELECT @RowIdTableA = (MAX(TableAId) - (MAX(TableAId) * @TopPercent)) FROM TableA
SELECT @RowIdTableB = (MAX(TableBId) - (MAX(TableBId) * @TopPercent)) FROM TableB

SELECT *
FROM TableA a
INNER JOIN TableB b ON a.KeyId = b.KeyId
WHERE a.Id > @RowIdTableA AND b.Id > @RowIdTableB AND
      a.SomeOtherCriteria = 'Whatever'

#14

To display last 3 rows without using order by:

显示最后3行，不使用order by:

select * from Lms_Books_Details where Book_Code not in 
 (select top((select COUNT(*) from Lms_Books_Details ) -3 ) book_code from Lms_Books_Details) 

#15

Try using the EXCEPT syntax.
Something like this:

尝试使用除语法之外的内容。是这样的:

   SELECT * 
    FROM   clientDetails 
    EXCEPT 
    (SELECT TOP (numbers of rows - how many rows you want) * 
     FROM   clientDetails)