SQL Server TOP and FETCH and PostgreSQL LIMIT and OFFSET
Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
---|---|---|---|
|
|
PostgreSQL doesn’t support TOP. |
SQL Server Usage
SQL Server supports two options for limiting and paging result sets returned to the client. TOP
is a legacy, proprietary T-SQL keyword that is still supported due to its wide usage. The ANSI compliant syntax of FETCH
and OFFSET
were introduced in SQL Server 2012 and are recommended for paginating results sets.
TOP
The TOP (n)
operator is used in the SELECT
list and limits the number of rows returned to the client based on the ORDER BY
clause.
Note
When TOP
is used with no ORDER BY
clause, the query is non-deterministic and may return any rows up to the number specified by the TOP
operator.
You can use TOP (n)
with two modifier options:
-
TOP (n) PERCENT
is used to designate a percentage of the rows to be returned instead of a fixed maximal row numberlimit (n)
. When you usePERCENT
,n
can be any value from 1-100. -
TOP (n) WITH TIES
is used to allow overriding the n maximal number or percentage of rows specified in case there are additional rows with the same ordering values as the last row.
If you use TOP (n)
without WITH TIES
and there are additional rows that have the same ordering value as the last row in the group of n rows, the query is also non-deterministic because the last row may be any of the rows that share the same ordering value.
Syntax
ORDER BY <Ordering Expression> [ ASC | DESC ] [ ,...n ] OFFSET <Offset Expression> { ROW | ROWS } [FETCH { FIRST | NEXT } <Page Size Expression> { ROW | ROWS } ONLY ]
Examples
The following example creates the OrderItems table.
CREATE TABLE OrderItems ( OrderID INT NOT NULL, Item VARCHAR(20) NOT NULL, Quantity SMALLINT NOT NULL, PRIMARY KEY(OrderID, Item) );
INSERT INTO OrderItems (OrderID, Item, Quantity) VALUES (1, 'M8 Bolt', 100), (2, 'M8 Nut', 100), (3, 'M8 Washer', 200), (3, 'M6 Locking Nut', 300);
The following example retrieves the 3 most ordered items by quantity.
-- Using TOP SELECT TOP (3) * FROM OrderItems ORDER BY Quantity DESC; -- USING FETCH SELECT * FROM OrderItems ORDER BY Quantity DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
For the preceding example, the result looks as shown following.
OrderID Item Quantity 3 M6 Locking Nut 300 3 M8 Washer 200 2 M8 Nut 100
The following example includes rows with ties.
SELECT TOP (3) WITH TIES * FROM OrderItems ORDER BY Quantity DESC;
For the preceding example, the result looks as shown following.
OrderID Item Quantity 3 M6 Locking Nut 300 3 M8 Washer 200 2 M8 Nut 100 1 M8 Bolt 100
The following example retrieves half the rows based on quantity.
SELECT TOP (50) PERCENT * FROM OrderItems ORDER BY Quantity DESC;
For the preceding example, the result looks as shown following.
OrderID Item Quantity 3 M6 Locking Nut 300 3 M8 Washer 200
For more information, see SELECT - ORDER BY Clause (Transact-SQL)
PostgreSQL Usage
Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) supports the non-ANSI compliant but popular with other engines LIMIT… OFFSET
operator for paging results sets.
The LIMIT
clause limits the number of rows returned and doesn’t require an ORDER BY
clause, although that would make the query non-deterministic.
The OFFSET
clause is zero-based, similar to SQL Server and used for pagination. OFFSET 0
is the same as omitting the OFFSET
clause, as is OFFSET
with a NULL argument.
Syntax
SELECT select_list
FROM table_expression
[ ORDER BY ... ]
[ LIMIT { number | ALL } ] [ OFFSET number ]
Migration Considerations
You can use the LIMIT… OFFSET
syntax to replace the functionality of TOP(n)
and FETCH… OFFSET
in SQL Server. It is automatically converted by the AWS Schema Conversion Tool (AWS SCT) except for the WITH TIES
and PERCENT
modifiers.
To replace the PERCENT
option, first calculate how many rows the query returns and then calculate the fixed number of rows to be returned based on that number.
Note
Because this technique involves added complexity and accessing the table twice, consider changing the logic to use a fixed number instead of percentage.
To replace the WITH TIES
option, rewrite the logic to add another query that checks for the existence of additional rows that have the same ordering value as the last row returned from the LIMIT
clause.
Note
Because this technique introduces significant added complexity and three accesses to the source table, consider changing the logic to introduce a tie-breaker into the ORDER BY
clause.
Examples
The following example creates the OrderItems table.
CREATE TABLE OrderItems ( OrderID INT NOT NULL, Item VARCHAR(20) NOT NULL, Quantity SMALLINT NOT NULL, PRIMARY KEY(OrderID, Item) );
INSERT INTO OrderItems (OrderID, Item, Quantity) VALUES (1, 'M8 Bolt', 100), (2, 'M8 Nut', 100), (3, 'M8 Washer', 200), (3, 'M6 Locking Nut', 300);
The following example retrieves the three most ordered items by quantity.
SELECT * FROM OrderItems ORDER BY Quantity DESC LIMIT 3 OFFSET 0;
For the preceding example, the result looks as shown following.
OrderID Item Quantity 3 M6 Locking Nut 300 3 M8 Washer 200 1 M8 Bolt 100
The following example includes rows with ties.
SELECT * FROM ( SELECT * FROM OrderItems ORDER BY Quantity DESC LIMIT 3 OFFSET 0 ) AS X UNION SELECT * FROM OrderItems WHERE Quantity = ( SELECT Quantity FROM OrderItems ORDER BY Quantity DESC LIMIT 1 OFFSET 2 ) ORDER BY Quantity DESC
For the preceding example, the result looks as shown following.
OrderID Item Quantity 3 M6 Locking Nut 300 3 M8 Washer 200 2 M8 Nut 100 1 M8 Bolt 100
The following example retrieves half the rows based on quantity.
CREATE or replace FUNCTION getOrdersPct(int) RETURNS SETOF OrderItems AS $$ SELECT * FROM OrderItems ORDER BY Quantity desc LIMIT (SELECT COUNT(*)*$1/100 FROM OrderItems) OFFSET 0; $$ LANGUAGE SQL;
SELECT * from getOrdersPct(50); or SELECT getOrdersPct(50); OrderID Item Quantity 3 M6 Locking Nut 300 3 M8 Washer 200
Summary
SQL Server | Aurora PostgreSQL | Comments |
---|---|---|
|
|
|
|
Not supported |
See examples for workaround |
|
Not supported |
See examples for workaround |
|
|
For more information, see LIMIT and OFFSET