0

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?

jarlh
  • 42,561
  • 8
  • 45
  • 63
ike3
  • 1,760
  • 1
  • 17
  • 26

2 Answers2

1

The smart way to solve this is to actually put the actual row in the history table too. Then you just look in the history when you want all the history, and you can look at actual when you want fadt lookup of current items.

Or you scrap the actual table altogether. Thats an architectural desision though..

Mattias Åslund
  • 3,877
  • 2
  • 18
  • 17
  • I think this is the best solution but you are right - I cannot change the architecture. – ike3 May 24 '16 at 06:29
1

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
dcieslak
  • 2,697
  • 1
  • 12
  • 19
  • Thanks for pointing out on the DB vendor. It is the PostgreSQL DB. Added to the question. – ike3 May 24 '16 at 06:21