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
This question is related to
sql
sql-server
performance
sql-server-2008
Try using the EXCEPT
syntax.
Something like this:
SELECT *
FROM clientDetails
EXCEPT
(SELECT TOP (numbers of rows - how many rows you want) *
FROM clientDetails)
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;
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)
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)
This may not be quite the right fit to the question, but…
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.
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.
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
use desc with orderby at the end of the query to get the last values.
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.
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
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]
You can make SQL server to select last N rows using this SQL:
select * from tbl_name order by id desc limit N;
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'
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)
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
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.
Source: Stackoverflow.com