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

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.

CodePudding user response：

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