How to skip the first n rows in sql query

Question

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

Answer 1

For SQL Server 2012 and above, use this:

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

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

Answer 2

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

Answer 3

Query: 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 rows of Table, where rownumber is starting from @N + 1.

Answer 4

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

Answer 5

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 ORDER BY ID)
ORDER BY ID;

This approach will work in any SQL version. You need to stablish some order (by Id for example) so all rows are provided in a predictable manner.

Answer 6

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
   

Answer 7

What about this:

SELECT * FROM table LIMIT 50 OFFSET 1

Answer 8

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

Answer 9

PostgreSQL: OFFSET without LIMIT

This syntax is supported, and it is in my opinion the cleanest API compared to other SQL implementations as it does not introduce any new keywords:

SELECT * FROM mytable ORDER BY mycol ASC OFFSET 1

that should definitely be standardized.

The fact that this is allowed can be seen from: https://www.postgresql.org/docs/13/sql-select.html since LIMIT and OFFSET can be given independently, since OFFSET is not a sub-clause of LIMIT in the syntax specification:

    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]

SQLite: negative limit

OFFSET requires LIMIT in that DBMS, but dummy negative values mean no limit. Not as nice as PostgreSQL, but it works:

SELECT * FROM mytable ORDER BY mycol ASC LIMIT -1 OFFSET 1

Asked at: SQLite with skip (offset) only (not limit)

Documented at: https://sqlite.org/lang_select.html

If the LIMIT expression evaluates to a negative value, then there is no upper bound on the number of rows returned.

MySQL: use a huge limit number

Terrible API design, the documentation actually recommends it:

SELECT * FROM tbl LIMIT 1,18446744073709551615;

Asked at: MySQL skip first 10 results

Node.js Sequelize ORM implements it

That ORM allows e.g. findAll({offset: without limit:, and implements workarounds such as the ones mentioned above for each different DBMS.

Answer 10

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.

Answer 11

DECLARE @Skip int= 2, @Take int= 2

SELECT * FROM TABLE_NAME
ORDER BY Column_Name 
OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLY

Answer 12

try below query it's work

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

Hope this will help

Answer 13

You can also use OFFSET to remove the 1st record from your query result like this-

Example - find the second max salary from the employee table

select distinct salary from employee order by salary desc limit 1 OFFSET 1

Answer 14

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