Query to get the previous value for a group of numbers

I have a table Metadata with columns code,revno and idno. revno will be unique. Same idno may exist for multiple revisions. Now my requirement is, For a set of given revno, I need to query the previous revno uniquely if available as grouped. Picture 1 contains data. if 3,4 & 7 are query revnos, then previous revno I need to get as in picture 2 as grouped. For 3 we get 1, because idno & code matches to it. For 4 we get null, because no previous data exists for idno 2 & code Person. For 7 we get 6 because, idno 2 & code Animal matches.

Answer

You can use a CTE to generate the highest revno prior to the current revno with the same idno and code, and then select the rows from the CTE which are the ones you want:

WITH CTE AS (
  SELECT revno, 
         LAG(revno) OVER (partition BY idno, code ORDER BY revno) AS prev
  FROM Metadata
)
SELECT *
FROM CTE
WHERE revno IN (3, 4, 7)
ORDER BY revno

Output

REVNO   PREV
3       1
4       null
7       6

Demo on dbfiddle