First/last (aggregate)

Library Snippets

First (aggregate)

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

This aggregate function returns the value from the first or last input row in each group, ignoring NULL rows. (NULLs are ignored automatically by the STRICT declaration, documented here)

The result depends on the sort order of input rows, which is arbitrary for unsorted input.
Function and aggregate function can be PARALLEL SAFE in Postgres 9.6 or later. Parallelism is never used when an aggregate function is used with ORDER BY. The manual:

Partial (including parallel) aggregation is currently not supported for ordered-set aggregates. Also, it will never be used for aggregate calls that include DISTINCT or ORDER BY clauses, since those semantics cannot be supported during partial aggregation.

The aggregate function returns an arbitrary result (possibly using parallelism) for unsorted input, and a deterministic result (not using parallelism) for sorted input.

Example

test=# select first(column1), last(column1) from (values (null),(1),(3),(null)) as x;
 first | last 
-------+------
     1 |    3

SQL version

This is an SQL-language implementation with no external dependencies.

-- Create a function that always returns the first non-NULL value:
CREATE OR REPLACE FUNCTION public.first_agg (anyelement, anyelement)
  RETURNS anyelement
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT $1';

-- Then wrap an aggregate around it:
CREATE AGGREGATE public.first (anyelement) (
  SFUNC    = public.first_agg
, STYPE    = anyelement
, PARALLEL = safe
);

-- Create a function that always returns the last non-NULL value:
CREATE OR REPLACE FUNCTION public.last_agg (anyelement, anyelement)
  RETURNS anyelement
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT $2';

-- Then wrap an aggregate around it:
CREATE AGGREGATE public.last (anyelement) (
  SFUNC    = public.last_agg
, STYPE    = anyelement
, PARALLEL = safe
);

Initial idea: http://archives.postgresql.org/pgsql-hackers/2006-03/msg01324.php with a couple of corrections.

Fast C version

A faster version written in C is available in PGXN, called first_last_agg: http://pgxn.org/dist/first_last_agg/