In Issue #70 an algorithm was described enabling the first N rows in a grouped query. This query returned on N rows for each group.

A much simpler solution to the original Top N with group by in Issue #70 was suggested Alistair Eaves. The SQL code for the original sets up this better solution.

	SELECT *
	FROM table70
	WHERE id IN (
	   SELECT id
	   FROM table70 AS topn
	   WHERE topn.query = table70.query
	   ORDER by score, id
	   LIMIT 2 )
	ORDER BY query

It was also brought to my attention by Troels Arvin that not everyone thinks of "Top N" in this way. In other definitions of Top N, you may have more than N rows when there are tie conditions for the values in the group. For example, from Troel's RDBMS Writings, given the person table, the three youngest people might be four people of two ages.

	SELECT * FROM person ORDER BY age ASC;
	+----------+-------------+-----+
	|PERSON_ID | PERSON_NAME | AGE |
	+----------+-------------+-----+
	|        7 | Hilda       |  12 |
	|        8 | Bill        |  12 |
	|        4 | Joe         |  23 |
	|        2 | Veronica    |  23 |
	|        3 | Michael     |  27 |
	|        9 | Marianne    |  27 |
	|        1 | Ben         |  50 |
	|       10 | Michelle    |  50 |
	|        5 | Irene       |  77 |
	|        6 | Vivian      |  77 |
	+----------+-------------+-----+

	+----------+-------------+-----+
	|PERSON_ID | PERSON_NAME | AGE |
	+----------+-------------+-----+
	|        7 | Hilda       |  12 |
	|        8 | Bill        |  12 |
	|        4 | Joe         |  23 |
	|        2 | Veronica    |  23 |
	+----------+-------------+-----+

The difference depends on exactly what you want in your query. Using ORDER BY with LIMIT N will give you exactly N rows. You will have to write a more complex function to be able to handle the tie situations in a consistent and logical way.

Contributors: elein at varlena.com troels arvin at arvin.dk aeaves at contextworld.com

SQL 2003 Standards Approved Notes on SQL Standards 17-Apr-2004

As you probably saw in Robert Treat's Weekly news, the SQL2003 was finally published. An overview of some of the changes can be seen SQL:2003 Has Been Published by Andrew Eisenberg, et al. (You may need to purchase this document or be an ACM member to access this document.)

In this article, I will informally review some of the changes reviewed by Eisenberg, et al. to see how the PostgreSQL addresses these standards. The last four items in the list below are not covered in this article. They will be covered in a future article.

Statements about whether PostgreSQL supports or does not support an element is based on 7.4 at this time. Plans for 7.5 are not addressed. Quoted remarks are from the SQL:2003 Has Been Published Document.

Overview Summarized into an Executive Summary of Changes

Change	PostgreSQL
BIT and BITVARYING data types removed	Supported
BIGINT data type added	Supported
Permissions on running Procedures	Supported
Parameter syntax in Procedures	*Not Supported
Extensions added to CREATE TABLE AS	Supported
Sequence Generator Added	*Supported
Identity Column type	*Supported
Generated Column type	*Not Supported
Extensions added to CREATE TABLE LIKE	Not Supported
TABLE functions added	*Not Supported
MERGE statement added	Not Supported
MULTISET data type added	Not Supported
XML data type added	Not Supported
* Indicates partial or work around support

BIT and BITVARYING data types removed

	create table bits ( b bit, bv bit varying);
	insert into bits values ( '1'::bit, '1'::bit varying);
	insert into bits values ( '101'::bit, '101'::bit varying);
	insert into bits values ( 'xF'::bit, 'xF'::bit varying);
	elein=# select * from bits;
 	b |  bv  
	---+------
 	1 | 1
 	1 | 101
 	1 | 1111
	(3 rows)

BIGINT data type added

Permissions on running Procedures

Parameter syntax in Procedures

PostgreSQL does not support named parameters in its function definitions so it does not support this syntax which uses named parameters. However, the underlying problem of conflicting names is solved with PostgreSQL with the use of the ALIAS declaration of the parameters in plpgsql. The example below shows the different ways this is used. Both functions are only minimally defined in order to show the use of the named parameter and alias.

SQL2003	PostgreSQL
CREATE FUNCTION getdepname	CREATE FUNCTION depname
(deptno char(3))	(char(3))
RETURNS varchar(20)	RETURNS varchar(20)
	AS '
	DECLARE;
	dno as alias for $1;
	BEGIN
select deptname	select deptname
from dept	from dept
where deptno=getdepname.depname;	where deptno=dno;
	END;
	' language 'plpgsql';

Extensions added to CREATE TABLE AS

	elein=# select * from yyy;
	 one | two | data 
	-----+-----+------
	   2 |     |    3
	   3 |     |    4
	   4 |     |    5
	(3 rows)
	
	elein=# create table xxx (a, b, c) as (select * from yyy);
	SELECT
	elein=# select * from xxx;
	 a | b | c 
	---+---+---
	 2 |   | 3
	 3 |   | 4
	 4 |   | 5
	(3 rows)

An interesting note in the SQL2003: Published document is that

the syntax of the CREATE TABLE AS statement is similar to what is known as Materialized Query Tables (MQTs) or Materialized Views in many commercial products, the semantics are different.

Sequence Generator Added

• STARTS WITH n
• INCREMENT BY n
• MINVALUE n
• MAXVALUE n
• [NO] CYCLE

Identify Column type

	CREATE TABLE parts (
	   partnum INTEGER GENERATED ALWAYS AS IDENTITY
		( START WITH 100 INCREMENT BY 10 MIN VALUE 10),
		partname	VARCHAR(40)
	); 

	CREATE TABLE parts (
	   partnum SERIAL,
	   partname	VARCHAR(40)
	);
	ALTER SEQUENCE 'parts_partnum_seq' 
		START WITH 100 INCREMENT BY 10 MIN VALUE 10;

PostgreSQL allows you to say SERIAL or SERIAL8 where the spec requires that you declare the column as INTEGER AS IDENTITY or BIGINT AS IDENTITY.

An interesting limitation in the specifications is that one table can have at most one IDENTITY column. In PostgreSQL you may have as many SERIAL columns as you like in a table.

There is one more option on the 2003 specification which is GENERATED ALWAYS or GENERATED BY DEFAULT. The first raises an error if any value inserted into the column other than DEFAULT. The second option will allow assignment of the value in an UPDATE or INSERT statement. PostgreSQL supports the second option by default only and does not support the GENERATED ALWAYS option.

Generated Column type

CREATE TABLE employees (
	emp_id integer,
	salary decimal(7,2),
	bonus decimal(7,2),
	total_com GENERATED ALWAYS
	   AS (SALARY + BONUS)
	);

	insert into employees values (1234, 75000, 1500, DEFAULT );

This behavior is similar to virtual columns. Virtual columns had more or less the same declaration, but the value was not stored. It was calculated on the fly for each selection. Virtual columns were supported in Illustra. PostgreSQL does not support virtual columns.

PostgreSQL does not support generated column types in the CREATE TABLE definitions. The same behavior can be created using a TRIGGER function. With a TRIGGER function, the functionality is less limiting.

Extensions added to CREATE TABLE LIKE

The CREATE TABLE LIKE statement has been extended to include the addition of GENERATED columns and IDENTITY columns. PostgreSQL does not support the syntax in CREATE TABLE so it does not support it in CREATE TABLE LIKE.

It is important to note that in PostgreSQL when creating a table LIKE another table that has a serial column, a new sequence is not created. That must be done by a separate CREATE SEQUENCE and ALTER TABLE to set the default for the column to be the nextval() of the column.

Contributors: elein at varlena.com