CODEWITHSUNDEEP
sqlsql-serverperformancesql-server-2008
Diego
Diego

Reputation: 16714

SQL Server SELECT LAST N Rows

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?

EDIT

Sorry duplicated question of this one

Upvotes: 214

Views: 1032361

Answers (22)

gabrielkolbe
gabrielkolbe

Reputation: 145

IF you use php and mysql combine it like so:

$find = $db->prepare("SELECT count(id) as countaggre FROM analytics_aggregate");
      $find->execute();
      $countaggregates = $find->fetch(PDO::FETCH_ASSOC);
      if($countaggregates['countaggre'] > 12) { $start = $countaggregates['countaggre'] - 12; } else { $start = 0; }
   
      $find = $db->prepare("SELECT * FROM analytics_aggregate ORDER BY id ASC LIMIT ".$start.", 12");

Upvotes: 0

Niru Mukund Shah
Niru Mukund Shah

Reputation: 4733

You can get MySQL, PostgreSQL, and SQLite (but not SQL Server) to select the last N rows with the following query:

select * from tbl_name order by id desc limit N;

Upvotes: 161

Noor
Noor

Reputation: 36

SELECT * FROM TABLENAME WHERE COLUMNAME = SOMECONDITION ORDER BY COL1 DESC, COL2 DESC FETCH FIRST 1 ROWS ONLY;

Upvotes: 0

Chris Singleton
Chris Singleton

Reputation: 165

I always use a CTE to delete the very last rows like this. You can also use ROW_NUMBER() to count rows as well.

;WITH CTE
AS
(
SELECT *, COUNT(schoolBoundaryID) OVER(ORDER BY schoolBoundaryID DESC) AS 
SchoolBoundaryCount FROM [dbo].[SchoolBoundary] 
)
-- Deletes the last 80,42 rows.
DELETE FROM CTE WHERE SchoolBoundaryCount < 8043

Upvotes: 0

iku
iku

Reputation: 1094

In order to get the result in ascending order

SELECT n.*
FROM
(
    SELECT *
    FROM MyTable
    ORDER BY id DESC
    LIMIT N
) n
ORDER BY n.id ASC

Upvotes: 2

Kaede
Kaede

Reputation: 198

I stumpled acros this issue while using SQL server What i did to resolve it is order the results descending and giving row number to the results of that, After i filtered the results and turned them around again.

  SELECT * 
  FROM (
    SELECT *
          ,[rn] = ROW_NUMBER() OVER (ORDER BY [column] DESC)
    FROM [table]
  ) A 
  WHERE A.[rn] < 3
  ORDER BY [column] ASC

Easy copy paste answer

Upvotes: 0

olafk
olafk

Reputation: 69

MS doesn't support LIMIT in t-sql. Most of the times i just get MAX(ID) and then subtract.

select * from ORDERS where ID >(select MAX(ID)-10 from ORDERS)

This will return less than 10 records when ID is not sequential.

Upvotes: 4

Sara
Sara

Reputation: 37

use desc with orderby at the end of the query to get the last values.

Upvotes: 2

Hakan Fıstık
Hakan Fıstık

Reputation: 19521

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

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)

Upvotes: 14

ABI
ABI

Reputation: 1754

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

This code took 0s.

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

Upvotes: 65

CodeCowboyOrg
CodeCowboyOrg

Reputation: 3043

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.

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.

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.

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'

Upvotes: 4

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12575

First you most get record count from 

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

And then :

In 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

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

Upvotes: 10

JonVD
JonVD

Reputation: 4268

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:

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

Upvotes: 43

Slava
Slava

Reputation: 3505

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

Upvotes: 1

Basil Bourque
Basil Bourque

Reputation: 340240

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.

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

Upvotes: 1

timberhill
timberhill

Reputation: 67

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

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

Upvotes: 2

mayur godhani
mayur godhani

Reputation: 11

Try using the EXCEPT syntax.
Something like this: 

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

Upvotes: 0

fth
fth

Reputation: 71

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

Upvotes: 7

Prafulla Sutradhar
Prafulla Sutradhar

Reputation: 191

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 

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)

Upvotes: 19

Dzamo Norton
Dzamo Norton

Reputation: 1389

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.

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.

Upvotes: 4

abhinay
abhinay

Reputation: 19

To display last 3 rows without using 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)

Upvotes: -1

AdaTheDev
AdaTheDev

Reputation: 147374

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

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.

Upvotes: 6

Related Questions