In IBM DB2, you can use the OFFSET and FETCH FIRST (or NEXT) clause to limit the rows returned by the query.
In SQL Server 2012 or later, you can also use the OFFSET and FETCH FIRST (or NEXT) clause but the ORDER BY clause must be also specified while this is optional in DB2.
Note that you can also use the TOP clause in SQL Server when you need to limit the number of rows only without the offset.
Consider the following sample table:
CREATE TABLE products (name VARCHAR(30)); INSERT INTO products VALUES ('Apple'); INSERT INTO products VALUES ('Melon'); INSERT INTO products VALUES ('Orange');
IBM DB2:
-- Select 1 row only but with offset 2 SELECT name FROM products ORDER BY name OFFSET 2 ROWS FETCH FIRST 1 ROWS ONLY; /* Orange */ -- Without offset SELECT name FROM products ORDER BY name DESC FETCH FIRST 1 ROWS ONLY; /* Orange */ -- Without ORDER BY and OFFSET SELECT name FROM products FETCH FIRST 1 ROWS ONLY; /* Apple */ -- Shorthand syntax for selecting 1 row SELECT name FROM products FETCH FIRST ROW ONLY; /* Apple */
SQL Server:
-- Select 1 row only but with offset 2 SELECT name FROM products ORDER BY name OFFSET 2 ROWS FETCH FIRST 1 ROWS ONLY; /* Orange */ -- OFFSET must be specified SELECT name FROM products ORDER BY name DESC FETCH FIRST 1 ROWS ONLY; /* Msg 153, Level 15, State 2, Line 1 */ /* Invalid usage of the option FIRST in the FETCH statement. */ -- Using OFFSET 0 if no offset required SELECT name FROM products ORDER BY name DESC OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY; /* Orange */ -- Both ORDER BY and OFFSET must be specified SELECT name FROM products FETCH FIRST 1 ROWS ONLY; /* Msg 153, Level 15, State 2, Line 1 */ /* Invalid usage of the option FIRST in the FETCH statement. */ -- Using ORDER BY (SELECT NULL) and OFFSET 0 SELECT name FROM products ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY; /* Apple */ -- Or you can just use the TOP clause SELECT TOP 1 name FROM products; /* Apple */
Note that SQL Server requires both ORDER BY and OFFSET clauses to be specified if you use the FETCH FIRST clause.
For more information, see IBM DB2 to SQL Server Migration.