I have an audit table where entities are stored by id and an associated revision number and revision type (this is a Hibernate Envers audit table).
E.g.
id | rev | revtype | foo_description |
---|---|---|---|
10 | 1 | 0 | 10 foo v1 |
10 | 3 | 1 | 10 foo v2 |
10 | 4 | 1 | 10 foo v3 |
20 | 2 | 0 | 20 foo v1 |
20 | 4 | 1 | 20 foo v2 |
How can I query this table so that I get the latest revision of each id that is less than a specified revision, e.g. rev=4?
From the example table above, the result of the query for latest revisions less than rev=4 should be:
id | rev | revtype | foo_description |
---|---|---|---|
10 | 3 | 1 | 10 foo v2 |
20 | 2 | 0 | 20 foo v1 |
I’m using a MySQL DB, version 5.7.
Advertisement
Answer
in mysql 8+:
select * from ( select * , row_number() over (partition by id order by rev desc) rn from tablename where rev < 4 ) t where rn = 1
in mysql 5.7:
select * from mytable t1 where (id,rev) = ( select id, rev from mytable t2 where t2.rev < 4 and t1.id = t2.id order by rev desc limit 1 )
db<>fiddle here