SQL Server SELECT LAST N Rows

Question

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

User · Answer

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

User · Answer

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

User · Answer

Maybe a little late  but here is a simple select that  solve your question   SELECT   FROM  TABLE  T ORDER BY  T ID TABLE  DESC LIMIT 5

User · Answer

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

User · Answer

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  gt   RowIdTableA AND b Id  gt   RowIdTableB AND       a SomeOtherCriteria    Whatever

User · Answer

Try using the EXCEPT syntax  Something like this       SELECT        FROM   clientDetails      EXCEPT       SELECT TOP  numbers of rows - how many rows you want          FROM   clientDetails

User · Answer

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  lt  10 BEGIN        FETCH PRIOR FROM MYTESTCURSOR INTO  MYVAR         print  MYVAR          SET  step    step   1  END    CLOSE MYTESTCURSOR DEALLOCATE MYTESTCURSOR

User · Answer

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

User · Answer

select   from  select top 6   from vwTable order by Hours desc  T order by Hours

User · Answer

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

User · Answer

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  lt   5

User · Answer

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

User · Answer

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  gt  select MAX ID -10 from ORDERS    This will return less than 10 records when ID is not sequential

User · Answer

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

User · Answer

First you most get record count from     Declare  TableRowsCount Int  select  TableRowsCount  COUNT    from  lt Your Table gt    And then    In SQL Server 2012  SELECT   FROM   lt Your Table gt  As L ORDER BY L  lt your Field gt  OFFSET  lt  TableRowsCount- N gt  ROWS FETCH NEXT  N ROWS ONLY    In SQL Server 2008  SELECT   FROM    SELECT ROW NUMBER   OVER ORDER BY ID  AS sequencenumber    FROM   lt Your Table gt      Order By  lt your Field gt    AS TempTable WHERE sequencenumber  gt   TableRowsCount- N

User · Answer

You can make SQL server to select last N rows using this SQL   select   from tbl name order by id desc limit N

User · Answer

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

User · Answer

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

[sql] SQL Server SELECT LAST N Rows

Examples related to sql

Examples related to sql-server

Examples related to performance

Examples related to sql-server-2008