Query historized data

Question

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

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.

Answer 1

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

Answer 2

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;