patternsqlModerate
group by and display contents of last row for each result
Viewed 0 times
resultlasteachgroupcontentsforanddisplayrow
Problem
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:
Sample data:
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....
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_idSolution
You can use
DISTINCT ON for this type of queries:SELECT DISTINCT ON (object_id) *
FROM data
ORDER BY object_id, dt DESC ;Code Snippets
SELECT DISTINCT ON (object_id) *
FROM data
ORDER BY object_id, dt DESC ;Context
StackExchange Database Administrators Q#64234, answer score: 11
Revisions (0)
No revisions yet.