[sql] How to skip the first n rows in sql query

I want to fire a Query "SELECT * FROM TABLE" but select only from row N+1. Any idea on how to do this?

Do you want something like in LINQ 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 SQL version.

This works with all DBRM/SQL, it 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

try below query it's work

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

Hope this will help

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 (following standard ANSI SQL)

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

They may meet your needs more or less.

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

SQL is not supposed to be used like this.

In relational databases, a table represents a relation, which is a set by definition. A set contains unordered elements.

Also, don't rely on the physical order of the records. The row order is not guaranteed by the RDBMS.

If the ordering of the records 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

In Faircom SQL (which is a pseudo MySQL), i can do this in a super simple SQL Statement, just as follows:

SELECT SKIP 10 * FROM TABLE ORDER BY Id

Obviously you can just replace 10 with any declared variable of your desire.

I don't have access to MS SQL or other platforms, but I'll be really surprised MS SQL doesn't support something like this.

In order to do this in SQL Server, you must order the query by a column, so you can specify the rows you want.

Example:

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

Use this:

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

https://stackoverflow.com/a/19669165/1883345

What about:

SELECT * FROM table LIMIT 50 OFFSET 1

I know it's quite late now to answer the query. But I have a little different solution than the others which I believe has better performance because no comparisons are performed in the SQL query only sorting is done. You can see its considerable performance improvement basically when value of SKIP is LARGE enough.

1. Best performance but only for SQL Server 2012 and above. Originally from @Majid Basirati's 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
   

2. Not as Good as the first one 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
   

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

I also loved @CarlosToledo's answer, it's not limited to any SQL Server version but it's missing Order By Clauses. Without them, it may return wrong results.

For SQL Server 2008 and later I would use Common Table Expressions for better 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