GREENPLUM: SELECT SYNTAX EXAMPLES

　

　

To join the table

films

with the table distributors:

SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM distributors d, films f WHERE f.did = d.did

To sum the column

length of all films and group the results by kind:

SELECT kind, sum(length) AS total FROM films GROUP BY kind;

To sum the column

length of all films, group the results by kind and show those group totals that are less than 5 hours:

SELECT kind, sum(length) AS total FROM films GROUP BY kind HAVING sum(length) < interval '5 hours';

Calculate the subtotals and grand totals of all sales for movie

kind and distributor.

SELECT kind, distributor, sum(prc*qty) FROM sales

GROUP BY ROLLUP(kind, distributor)

ORDER BY 1,2,3;

Calculate the rank of movie distributors based on total sales:

SELECT distributor, sum(prc*qty),

rank() OVER (ORDER BY sum(prc*qty) DESC)

FROM sale

　

　

　

GROUP BY distributor ORDER BY 2 DESC;

The following two examples are identical ways of sorting the individual results according to the contents of the second column (

name):

SELECT * FROM distributors ORDER BY name;

SELECT * FROM distributors ORDER BY 2;

The next example shows how to obtain the union of the tables

distributors and actors, restricting the results to those that begin with the letter W in each table. Only distinct rows are wanted, so the key word ALL is omitted:

SELECT distributors.name FROM distributors WHERE distributors.name LIKE 'W%' UNION SELECT actors.name FROM actors WHERE actors.name LIKE 'W%';

This example shows how to use a function in the

FROM clause, both with and without a column definition list:

CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$ SELECT * FROM distributors WHERE did = $1; $$ LANGUAGE SQL;

SELECT * FROM distributors(111);

　

CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$ SELECT * FROM distributors WHERE did = $1; $$ LANGUAGE SQL;

SELECT * FROM distributors_2(111) AS (dist_id int, dist_name text);