SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[Data](
    [RecordNumber] [int] NULL,
    [Data1] [nvarchar](10) NULL,
    [Data2] [nvarchar](10) NULL
);
INSERT INTO dbo.[Data]([RecordNumber],[Data1],[Data2]) VALUES
(1001, 'A', 'AA'),
(1002, 'B', 'BB'),
(1003, 'C', 'CC');
3 rows affected

CREATE TABLE [dbo].[Approval](
    [RecordNumber] [int] NOT NULL,
    [ApprovedByLevel] [int] NOT NULL,
    [ApprovedByName] [nvarchar](50) NOT NULL
); 
INSERT INTO [dbo].[Approval] VALUES
(1001, 1, 'Mr.A'),
(1001, 2, 'Mr.B'),
(1002, 2, 'Mr.AA'),
(1001, 3, 'Mr.C'),
(1002, 5, 'Mr.DD')
5 rows affected

select d.*, a.[ApprovedByLevel], a.[ApprovedByName]
from [Data] d left join (
  select *, 
    row_number() over (partition by [RecordNumber] order by [ApprovedByLevel] desc) rn
  from [Approval]
) a
on a.[RecordNumber] = d.[RecordNumber]
where a.rn = 1


RecordNumber
Data1
Data2
ApprovedByLevel
ApprovedByName


1001
A
AA
3
Mr.C

1002
B
BB
5
Mr.DD