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 AS $$ SELECT $1; $$; -- And then wrap an aggregate around it CREATE AGGREGATE public.first ( sfunc = public.first_agg, basetype = anyelement, stype = anyelement ); -- 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 AS $$ SELECT $2; $$; -- And then wrap an aggregate around it CREATE AGGREGATE public.last ( sfunc = public.last_agg, basetype = anyelement, stype = anyelement );
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/
