원본출처 : 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';
|