TOP
Description
Limits the rows returned in a query result set to a specified number of rows or percentage of rows. When you use TOP
with the ORDER BY
clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP
returns the first N number of rows in an undefined order. Use this clause to specify the number of rows returned from a SELECT
statement. Or, use TOP
to specify the rows affected by an INSERT
, UPDATE
, MERGE
, or DELETE
statement. (SQLServer TOP documentation)
Syntax in SQL Server
TOP (expression) [PERCENT] [ WITH TIES ]
Syntax in Snowflake
TOP <n>
Sample Source Patterns
To execute correctly the following samples it is required run the next CREATE TABLE
statement:
CREATE TABLE Cars(
Model VARCHAR(15),
Price MONEY,
Color VARCHAR(10)
);
INSERT Cars VALUES ('sedan', 10000, 'red'),
('convertible', 15000, 'blue'),
('coupe', 20000, 'red'),
('van', 8000, 'blue'),
('sub', 8000, 'green');
Common Case
SQL Server
SELECT TOP(1) Model, Color, Price
FROM Cars
WHERE Color = 'red'
Snowflake
SELECT
TOP 1
Model,
Color,
Price
FROM
Cars
WHERE
Color = 'red';
TOP using PERCENT
SQL Server
SELECT TOP(50)PERCENT Model, Color, Price FROM Cars
Snowflake
SELECT
TOP 50 !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
Model,
Color,
Price
FROM
Cars;
Since PERCENT
argument is not supported by Snowflake it is being removed from the TOP
clause, that's why the result of executing the query in Snowflake is not equivalent to SQL Server.
TOP WITH TIES
SQL Server
SELECT TOP(50)PERCENT WITH TIES Model, Color, Price FROM Cars ORDER BY Price;
Snowflake
SELECT
TOP 50 !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
Model,
Color,
Price
FROM
Cars
ORDER BY Price;
Since WITH TIES
argument is not supported by Snowflake it is being removed from the TOP
clause, that's why the result of executing the query in Snowflake is not equivalent to SQL Server.
Known Issues
1. PERCENT argument is not supported by Snowflake
Since the PERCENT
argument is not supported by Snowflake it is being removed from the TOP
clause and a warning is being added. Functional equivalence mismatches in the results could happen.
2. WITH TIES argument is not supported by Snowflake
Since the WITH TIES
argument is not supported by Snowflake it is being removed from the TOP
clause and a warning is being added. Functional equivalence mismatches in the results could happen.
Related EWIs
SSC-EWI-00040: THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE
Last updated