How to skip the first n rows in sql query

Question

I want to run the query " SELECT * FROM TABLE ", but select only from line N+1 . Any idea on how to do this?

Answer 1

Request: in sql-server

 DECLARE @N INT = 5 --Any random number SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RoNum , ID --Add any fields needed here (or replace ID by *) FROM TABLE_NAME ) AS tbl WHERE @N < RoNum ORDER BY tbl.ID 

This will give the rows of the table where rownumber starts with @N + 1 .

Answer 2

Use this:

 SELECT * FROM Sales.SalesOrderHeader ORDER BY OrderDate OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLY 

fooobar.com/questions/64414 / ...

Answer 3

SQL Server:

 select * from table except select top N * from table 

Oracle up to 11.2:

 select * from table minus select * from table where rownum <= N with TableWithNum as ( select t.*, rownum as Num from Table t ) select * from TableWithNum where Num > N 

Oracle 12.1 and later (ANSI SQL compliant)

 select * from table order by some_column offset x rows fetch first y rows only 

They can satisfy your needs more or less.

There is no direct way to do what you want using SQL. However, in my opinion, this is not a design flaw.

SQL should not be used this way.

In relational databases, a table represents a relationship that is defined by definition. The set contains unordered elements.

Also, do not rely on the physical order of entries . The row order is not guaranteed by the DBMS.

If the order of the entries is important, you'd better add a column such as "Num" to the table and use the following query. This is more natural.

 select * from Table where Num > N order by Num 

Answer 4

To do this in SQL Server, you must order the query in a column so that you can specify the rows you want.

Example:

 select * from table order by [some_column] offset 10 rows FETCH NEXT 10 rows only 

Answer 5

Do you want something like LINQ to skip 5 and take 10?

 SELECT TOP(10) * FROM MY_TABLE WHERE ID not in (SELECT TOP(5) ID From My_TABLE); 

This approach will work in any version of SQL.

Answer 6

I know that it’s rather late to answer the request. But I have a slightly different solution than others, which I believe have better performance, because the SQL query does not perform a comparison. You can see its significant performance improvement mainly when the SKIP value is large enough.

• Best performance , but only for SQL Server 2012 and above. Originally from the @Majid Basirati answer , which is worth mentioning again.

   DECLARE @Skip INT = 2, @Take INT = 2 SELECT * FROM TABLE_NAME ORDER BY ID ASC OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLY 

• Not as good as the first, but compatible with SQL Server 2005 and above.

   DECLARE @Skip INT = 2, @Take INT = 2 SELECT * FROM ( SELECT TOP (@Take) * FROM ( SELECT TOP (@Take + @Skip) * FROM TABLE_NAME ORDER BY ID ASC ) T1 ORDER BY ID DESC ) T2 ORDER BY ID ASC 

Answer 7

What about:

 SELECT * FROM table LIMIT 50 OFFSET 1 

Answer 8

This works with all DBRM / SQL, this is standard ANSI:

 SELECT * FROM owner.tablename A WHERE condition AND n+1 <= ( SELECT COUNT(DISTINCT b.column_order) FROM owner.tablename B WHERE condition AND b.column_order>a.column_order ) ORDER BY a.column_order DESC 

Answer 9

In Faircom SQL (which is pseudo MySQL), I can do this in a simple SQL statement, as shown below:

 SELECT SKIP 10 * FROM TABLE ORDER BY Id 

Obviously, you can simply replace 10 any declared variable of your desire.

I do not have access to MS SQL or other platforms, but I really will be surprised that MS SQL does not support something like this.

Answer 10

try to execute the request

 SELECT * FROM `my_table` WHERE id != (SELECT id From my_table LIMIT 1) 

Hope this helps

Answer 11

For SQL Server 2012 and later, the best method is @MajidBasirati's answer.

I also liked @CarlosToledo's answer, it is not limited to any version of SQL Server, but it lacks Order By clauses. Without them, this can lead to incorrect results.

For SQL Server 2008 and later, I would use Common Table Expressions to improve performance.

 -- This example omits first 10 records and select next 5 records ;WITH MyCTE(Id) as ( SELECT TOP (10) Id FROM MY_TABLE ORDER BY Id ) SELECT TOP (5) * FROM MY_TABLE INNER JOIN MyCTE ON (MyCTE.Id <> MY_TABLE.Id) ORDER BY Id