ike3 ike3 - 1 year ago 42
SQL Question

Actual/history table: select by version number

I have two DB tables in PostgresSQL database:

create table actual (id int, name text, version int)
create table history (id int, name text, version int, actual_id int)

When a record changes it is copied to the history table and the actual version increments. Rows cannot be deleted.

E.g. if we have 3 records
A1, B1, C1
(1 is the version number) and change B's name, then the actual table will contain
A1, B2, C1
and history -
. We could then change C's name and actual data will be
A1, B2, C3
and history -
B1, C1

How to select rows by name and version number? E.g.
version = 2 and name like '%'
should give us
A1, B2, C1
(A1 and C1 are still actual on version 2, they are unchanged from version 1).

I came up with an union select such as:

select id, name, version from
select id, name, version, id as actual_id from actual
union select id, name, version, actual_id from history
) q
where version <= 2 and name like '%'
group by actual_id
order by version desc;

Is it possible to do this without union (i.e. Hibernate doesn't support it) or using some more optimal way?

Answer Source

I am not sure what OP's db vendor is. However the below works on MS SQL:

select * from (
select row_number() over (partition by id order by version desc) rn, 
  select h.actual_id as id, h.name, h.version from history h
  union all
  select * from actual 
) x
where version <= 2 )  y
where rn = 1