Aggregate by selecting last element in postgres group by

Question

I have a table containing object statistics in postges with the following columns:

id integer
object_id integer
timestamp timestamp with time zone
dimension character varying
value integer

Note that it's using the entity-attribute-value (eav) pattern - entity is object_id, attribute is dimension, value is value.

A job runs nightly that collects values over six dimensions (specified by dimension) for each object (specified by object_id).

I'd like to build a reporting page that shows that most recent value for each dimension for each product. The query would look like this:

select object_id, dimension, FIRST(value) from pt_reading group by product_id, dimension order by product_id, dimension, timestamp desc;

FIRST would pick the first value which is the one I'd like since I'm sorting by timestamp desc. Unfortunately FIRST is not a valid aggregator function.

What's the best design pattern to do this? I could always fallback to writing it in python, but it seems like something that could be handled by the database. Also, please chime in if there's a different design pattern I should be using to do this.

For some explanation you might be interested in [this related answer](http://stackoverflow.com/questions/3800551/sql-select-first-row-in-each-group-by-group/7630564#7630564). — Erwin Brandstetter, Mar 08 '13 at 02:24

Clodoaldo Neto · Accepted Answer · 2013-03-07T23:27:57.733

8

select distinct on (object_id, dimension)
    object_id,
    dimension,
    value
from pt_reading
order by object_id, dimension, "timestamp" desc;

edited Mar 07 '13 at 23:27

answered Mar 07 '13 at 22:55

Clodoaldo Neto

118,695
26
233
260

Aggregate by selecting last element in postgres group by

1 Answers1