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)

Example

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

Portable SQL version

This is a portable SQL-language implementation with no external dependencies.

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

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement,
        parallel = safe
);

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

-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement,
        parallel = safe
);

Credit: 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 avaiblable in PGXN, called first_last_agg: http://pgxn.org/dist/first_last_agg/