Skip to content
Advertisement

SQL – Select rows with greatest revision that are less than a set revision

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

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement