The idea is as follows - in the base table

id tag_id value time 1 1 12 12:14 2 1 12 12:15 3 2 11 12:13

I want to get one last entry for all tags. those. with id 2 and id 3

desc limit does not seem to fit, and group by seems not

  • I recommend to make changes to the structure of the database. Logic says that sampling should be done on time. But what happens if the first record gets into the database, for example, at 23:59:59, f the next at 00:00:01 the next day? Optional use id? Well, you already have the practice (judging by the comments) of the appearance of holes. It remains to wait for the practice of re-initializing id (clumsy move, or something else). So, my advice to you, replace time with a timestamp. - Vitalts
  • group by , in my opinion, works great here too. Strange that you did not work ... Sql fiddler - ApInvent
  • yes it is timestamp - des1roer
  • you did not consider value. and from max (id) you should not jump - des1roer

2 answers 2

And id incremental? Then you can like this:

 SELECT tv.* FROM ( SELECT tag_id ,MAX(id) tv_id FROM tags_values tv GROUP BY tag_id ) x INNER JOIN tags_values tv ON tv.id = x.tv_id

If you start from time

  SELECT tv.* FROM ( SELECT tag_id AS tv_tag_id, MAX(f_timestamp) AS tv_timestamp FROM data_view tv WHERE f_timestamp >now() - interval '5 minutes' GROUP BY tag_id) x LEFT JOIN data_view tv ON tv.f_timestamp = x.tv_timestamp AND x.tv_tag_id = tv.tag_id

UPDATE

If this option is not suitable, then you can use LATERAL . It is in PostreSQL since 9.3

 SELECT y.* FROM ( SELECT tag_id FROM tags_values GROUP BY tag_id ) x, LATERAL ( SELECT * FROM tags_values tv WHERE tv.tag_id = x.tag_id ORDER BY tv.time DESC LIMIT 1 ) y
  • logically yes. but there are also breaks (encountered during partitioning). so max (time) is more appropriate - des1roer
  • with a small amendment it all worked - des1roer
  • Added an example for LATERAL - Donil
  • Is it better or worse than the original decision? - des1roer
  • First - no. With the condition that the condition is satisfied: the more id the more time. With your option, the problem may be that several changes took place immediately one after the other (with an accuracy of seconds, say) and what then? You will get several values ​​for one tag. The variant with LATERAL will allow to avoid it - Donil

Another solution:

  select * from ( select *, rank() OVER (PARTITION BY tag_id ORDER BY id DESC) from TABLENAME ) as foo where rank = 1