TOP

Applies to

• SQL Server

• Azure Synapse Analytics

Description

Some parts in the output code are omitted for clarity reasons.

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. (Transact-SQL TOP documentation)

Syntax in Transact-SQL

TOP (expression) [PERCENT] [ WITH TIES ]

To get more information about the TOP arguments please check the Transact-SQL TOP documentation.

Syntax in Snowflake

TOP <n> 

To get more information about TOP arguments please check the Snowflake TOP documentation.

Sample Source Patterns

To execute correctly the following samples it is required run the next CREATE TABLE statement:

Transact-SQL

IN -> SqlServer_01.sql

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');

Snowflake

OUT -> SqlServer_01.sql

CREATE OR REPLACE TABLE Cars (
    Model VARCHAR(15),
    Price NUMBER(38, 4),
    Color VARCHAR(10)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

INSERT INTO Cars VALUES ('sedan', 10000, 'red'),
('convertible', 15000, 'blue'),
('coupe', 20000, 'red'),
('van', 8000, 'blue'),
('sub', 8000, 'green');

Common Case

Transact-SQL

Query

IN -> SqlServer_02.sql

SELECT TOP(1) Model, Color, Price
FROM Cars
WHERE Color = 'red'

Result

Model | Color | Price
--------------------------
sedan |  red  | 10000.0000

Snowflake

Query

OUT -> SqlServer_02.sql

SELECT
TOP 1
Model,
Color,
Price
FROM
Cars
WHERE
Color = 'red';

Result

MODEL	COLOR	PRICE
sedan	red	10,000

TOP using PERCENT

Transact-SQL

Query

IN -> SqlServer_03.sql

SELECT TOP(50)PERCENT Model, Color, Price FROM Cars

Result

Model        |  Color  |  Prices
-----------------------------------
sedan        |  red    |  10000.0000
convertible  |  blue   |  15000.0000
coupe        |  green  |  20000.0000

Snowflake

Query

OUT -> SqlServer_03.sql

SELECT
TOP 50 !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
Model,
Color,
Price
FROM
Cars;

Result

MODEL	         COLOR        PRICE
sedan	         red          10,000
convertible      blue         15,000
coupe	         red          20,000
van	         blue         8,000
sub              green        8,000

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 Transact-SQL.

TOP WITH TIES

Transact-SQL

Query

IN -> SqlServer_04.sql

SELECT TOP(50)PERCENT WITH TIES Model, Color, Price FROM Cars ORDER BY Price;

Result

Model  |  Color   | Price
-------------------------------
van    |  blue    | 8000.0000
sub    |  green   | 8000.0000
sedan  |  red     | 10000.0000

Snowflake

Query

OUT -> SqlServer_04.sql

SELECT
TOP 50 !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
Model,
Color,
Price
FROM
Cars
ORDER BY Price;

Result

MODEL	        COLOR	    PRICE
sub	        green	    8,000
van	        blue	    8,000
sedan	        red	    10,000
convertible     blue	    15,000
coupe	        red	    20,000

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 Transact-SQL.

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

1. SSC-EWI-0040: Statement Not Supported.