Query historized data

Question

To describe my query problem, the following data is helpful:

enter image description here

A single table contains the columns ID (int), VAL (varchar) and ORD (int)

The values of VAL may change over time by which older items identified by ID won't get updated but appended. The last valid item for ID is identified by the highest ORD value (increases over time).

T0, T1 and T2 are points in time where data got entered.

How do I get in an efficient manner to the Result set?

A solution must not involve materialized views etc. but should be expressible in a single SQL-query. Using Postgresql 9.3.

possible duplicate of [How to select id with max date group by category in PostgreSQL?](http://stackoverflow.com/questions/16914098/how-to-select-id-with-max-date-group-by-category-in-postgresql) — Jakub Kania, Jan 29 '15 at 19:23

score 5 · Accepted Answer · edited May 23 '17 at 11:51

5

The correct way to select groupwise maximum in postgres is using DISTINCT ON

SELECT DISTINCT ON (id) sysid, id, val, ord
FROM my_table
ORDER BY id,ord DESC;

Fiddle

edited May 23 '17 at 11:51

Community

1
1

answered Jan 29 '15 at 19:23

Jakub Kania

15,665
2
37
47

Thorsten Kettner · Answer 2 · 2015-01-29T18:15:44.210

You want all records for which no newer record exists:

select *
from mytable
where not exists
(
  select *
  from mytable newer
  where newer.id = mytable.id
  and newer.ord > mytable.ord
)
order by id;

You can do the same with row numbers. Give the latest entry per ID the number 1 and keep these:

select sysid, id, val, ord
from
(
  select 
    sysid, id, val, ord, 
    row_number() over (partition by id order by ord desc) as rn
  from mytable
)
where rn = 1
order by id;

score 1 · Answer 3 · answered Jan 29 '15 at 18:13

Left join the table (A) against itself (B) on the condition that B is more recent than A. Pick only the rows where B does not exist (i.e. A is the most recent row).

SELECT last_value.* 
FROM my_table AS last_value
  LEFT JOIN my_table
    ON my_table.id = last_value.id
    AND my_table.ord > last_value.ord
WHERE my_table.id IS NULL;

SQL Fiddle

Query historized data

3 Answers3