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.

In Redshift, if your application allows foreign keys or invalid primary keys, it can cause queries to return incorrect results. For example, a SELECT DISTINCT query could return duplicate rows if the primary key column does not contain all unique values. (Redshift SQL Language Reference SELECT list)

Grammar Syntax

SELECT
[ TOP number ]
[ ALL | DISTINCT ] * | expression [ AS column_alias ] [, ...]

Sample Source Patterns

Top clause

Input Code:

Redshift

IN -> Redshift_01.sql

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
FROM employee;

Results

ID	NAME	MANAGER_ID
100	Carlos	null
101	John	100
102	Jorge	101
103	Kwaku	101
110	Liu	101

Output Code:

Snowflake

OUT -> Redshift_01.sql

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": "11/05/2024",  "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);

SELECT TOP 5 id, name, manager_id
FROM
    employee;

Results

ID	NAME	MANAGER_ID
100	Carlos	null
101	John	100
102	Jorge	101
103	Kwaku	101
110	Liu	101

ALL

Input Code:

Redshift

IN -> Redshift_02.sql

SELECT ALL manager_id
FROM employee;

Results

MANAGER_ID
null
100
101
101
101
102
103
103
103
104
104
102
104

Output Code:

Snowflake

OUT -> Redshift_02.sql

SELECT ALL manager_id
FROM
    employee;

Results

MANAGER_ID
null
100
101
101
101
102
103
103
103
104
104
102
104

DISTINCT

Input Code:

Redshift

IN -> Redshift_03.sql

SELECT DISTINCT manager_id
FROM employee;

Results

MANAGER_ID
null
100
101
102
103
104

Output Code:

Snowflake

OUT -> Redshift_03.sql

SELECT DISTINCT manager_id
FROM 
    employee;

Results

MANAGER_ID
null
100
101
102
103
104

Related EWIs

There are no known issues.