First/last (aggregate)
From PostgreSQL wiki
(Redirected from First (aggregate))
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/