SELECT list
Description
The SELECT list names the columns, functions, and expressions that you want the query to return. The list represents the output of the query. (Redshift SQL Language Reference SELECT list)
The query start options are fully supported in Snowflake. Just keep in mind that in Snowflake the DISTINCT
and ALL
options must go at the beginning of the query.
Grammar Syntax
SELECT
[ TOP number ]
[ ALL | DISTINCT ] * | expression [ AS column_alias ] [, ...]
Sample Source Patterns
Top clause
Input Code:
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
);
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
SELECT TOP 5 id, name, manager_id
INTO top_employees
FROM employee;
SELECT * FROM top_employees;
Output Code:
CREATE TABLE employee
(
id INT,
name VARCHAR(20),
manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "02/06/2025", "domain": "test" }}';
INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
CREATE TABLE IF NOT EXISTS top_employees AS
SELECT TOP 5 id, name, manager_id
FROM
employee;
SELECT * FROM
top_employees;
ALL
Input Code:
SELECT ALL manager_id
INTO manager
FROM employee;
Output Code:
CREATE TABLE IF NOT EXISTS manager AS
SELECT ALL manager_id
FROM
employee;
DISTINCT
Input Code:
SELECT DISTINCT manager_id
INTO manager
FROM employee;
Output Code:
CREATE TABLE IF NOT EXISTS manager AS
SELECT DISTINCT manager_id
FROM
employee;
Known Issues
There are no known issues.
Related EWIs.
There are no related EWIs.
Last updated