Heh, I just rolled my own first() and last() aggregators in Postgresql yesterday because I wanted something like this (googling for analytic queries found a somewhat related link that mentioned first() and last() aggregators as I was curious if my solution was in-line with SQL 2003's solution).

The process was a bit more interesting and difficult than I expected. It convinced me that Postgresql doesn't have a general solution for user-defined polymorphic aggregators because it doesn't allow polymorphic user-defined data types (you can't use "anyelement" as part of a "create type" construct -- at least in the versions I was dealing with, 8.2 and 8.3).

So I was able to create some polymorphic aggregators but can see cases where these approaches would not work (and the first route I tried failed).

The following test query:

select first(v), first_nonnull(v), last(v), last_nonnull(v)
from ( select NULL::text as v union all
    select 'one' as v union all select 'two' as v ) as q
union all
select first(v), first_nonnull(v), last(v), last_nonnull(v)
from ( select 'one'::text as v union all
    select 'two' as v union all select NULL as v ) as q;
[download]

produces the following results:

 first | first_nonnull | last | last_nonnull
-------+---------------+------+--------------
       | one           | two  | two
 one   | one           |      | two
(2 rows)
[download]

Sorry, no. Your solution is nothing like SQL 2003's analytic functions which come from Oracle. If you want a brief introduction to them, read http://www.orafusion.com/art_anlytc.htm. The syntax is..heinous. To use them effectively you often want to query a subquery. But they are very powerful and much more efficient than older solutions.

Actually, despite tilly's response, these are exactly like some of what Postgresql calls "window functions" which are exactly the same thing that Oracle calls "analytic functions" (Oracle uses "window functions" to refer to a subset of the "analytic functions").

One difference between my user-defined aggregate functions above and the standard window functions first_value() and last_value(), is that my aggregates can be used as simple aggregates (such as with just "GROUP BY") while first_value() and last_value() must have a window defined (via "OVER"). That is, my aggregates can be used with OVER or without OVER.

But the main reason I am responding is to note that my third attempt at defining first() and last() was much simpler. It is just like the _nonnull versions except you drop the word 'strict'.

create function first( one anyelement, two anyelement )
    returns anyelement
as $$ begin return one; end $$ language plpgsql;

create function last( one anyelement, two anyelement )
    returns anyelement
as $$ begin return two; end $$ language plpgsql;

create aggregate first( anyelement )(
    stype = anyelement
  , sfunc = first
);

create aggregate last( anyelement )(
    stype = anyelement
  , sfunc = last
);
[download]

Which makes me wonder what my first try (that didn't work) looked like.

I'm now working with newer Postgresql that supports window functions. But this version's first_value() doesn't support the standard "IGNORE NULLS" option which looked like the best solution for a problem I was working on today. So I asked google where my implementations were so I could just use them.

Swapping in these details again made me suspect that the first() and last() implementations could be as simple as the first_nonnull() and last_nonnull() implementations. Testing on this newer version of Postgresql showed that they could be.

- tye        

So something like

select jobname, status, timestamp, comment
from the_table
group by jobname
having timestamp = max(timestamp)
[download]

Michael

For the record, I hate that "feature". What it means is that if you accidentally leave a field that is in the SELECT out of the GROUP BY, you'll introduce a join. Depending on the query, this join can be very large and very poorly behaved. Particularly if you've left joined to a large table.

When I worked with Sybase I think I made this mistake every few months (with the left out field from a very large table), and inevitably didn't notice until my query had been grinding away for half an hour and the database was feeling the pressure. The database it usually happened on was used for production purposes, but luckily the consequences were only internally visible, and very few people were affected. Still it was my second greatest annoyance with Sybase. (They've since fixed my greatest annoyance at the time, which was page level locking.)

I agree with you for the general case (and I use a development tool that will flag such omissions as a defect).

But occasionally it is really useful - in particular for me as I write way more SQL (in stored procs, etc) than perl or any other client-side language these days.

Michael