[SOLVED] Actual/history table: select by version number

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 - B1. 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?

Solution

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, 
       name 
from 
(
  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