원본출처 : http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=190

Submitted on: 04-24-2001

Description:

first() and last() aggregate functions. They're useful when writing queries that have a group by clause. Often, you're group by a key or unique ID, and including other things that rely on that ID, such as first or last name. Some database systems (such as MS Access) include first() and last() functions that return the first in a set, or the last in a set.

SELECT id, first(fname), first(lname), sum(purchaseamt)

FROM pers

GROUP BY id

means the same thing as

SELECT id, fname, lname, sum(purchaseamt)

FROM pers

GROUP BY id, fname, lname

but is often easier to understand, and easier to translate from Access. In addition, you can use these functions to force a selection from a group other than the min() or max() -- just sort the table in the order you want, then grab the first entry w/first(), or last with last().

Code:

-- author: joel burton (jburton@scw.org)

-- public domain

CREATE FUNCTION "func_first" (text,text) RETURNS text AS '

begin

if length($1) > 0 then

return $1;

else

return $2;

end if;

end;'

LANGUAGE 'plpgsql' WITH ( iscachable );

CREATE AGGREGATE first ( BASETYPE = text, SFUNC = func_first, STYPE = text, INITCOND = '' );

COMMENT ON AGGREGATE first text IS 'Return first item in aggregate';

COMMENT ON FUNCTION "func_first" ( text,text ) IS 'Return first item in aggregate';

CREATE FUNCTION "func_last" (text,text) RETURNS text AS 'begin return $2; end;' language 'plpgsql' WITH ( iscachable );

COMMENT ON FUNCTION "func_last" ( text,text ) IS 'Return last item in aggregate';

CREATE AGGREGATE last ( BASETYPE = text, SFUNC = func_last, STYPE = text, INITCOND = '' );

COMMENT ON AGGREGATE last text IS 'Return last item in aggregate';