group by and display contents of last row for each result

Question

I've got some data of two objects and I want to retrieve the last row of both the objects, preferably in one query.

Table structure:

CREATE TABLE data
(
  pid serial NOT NULL,
  dt timestamp without time zone,
  object_id integer,
  info_1 numeric(10,8),
  speed numeric,
  CONSTRAINT dat_pid PRIMARY KEY (pid)
)

Sample data:

1, 2014-04-29 12:02:56, 8, ....
2, 2014-04-29 12:02:10, 8, ....
3, 2014-04-29 12:01:02, 8, ....
5, 2014-04-29 12:01:32, 6, ....
.....

I want a row per unique object ID, each row being the newest one available. I tried to achieve it by using this query, but it wants pid to be in the group by clause. But I don't want to group by the PID, but at the tracker id....

SELECT * 
FROM data
GROUP BY object_id

Answer 1

You can use DISTINCT ON for this type of queries:

SELECT DISTINCT ON (object_id) *
FROM data
ORDER BY object_id, dt DESC ;