HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

group by and display contents of last row for each result

Submitted by: @import:stackexchange-dba··
0
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:

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

Solution

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.