2

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
4

You can use DISTINCT ON for this type of queries:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.