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; 

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 

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) 

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)

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 

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

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.

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.

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' 

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.

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] 

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

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 

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.

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 

Try using the EXCEPT syntax.
Something like this:

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

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

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 

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

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"); 

I use this query:

SELECT * FROM ( SELECT * FROM MyTable ORDER BY id DESC LIMIT 10 )Var1 ORDER BY id ASC; 

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)