get latest ingested details

Question

in sql server, I am ingesting data into a table, tblMain
the table has columns as follows:
companyName,
book,
...,
year,
month,
day

each day, a snapshot of hundreds of rows are ingested.
so, for example, for yesterday, say, 20 companies were ingested
and today, say 30 companies were ingested.
the same company may be ingested each day...

Question:
How is it possible to have a sql query to get the latest companyName details.
for example, if company123 was ingested on year=2022, month=05, day=22 and day=28
I would like to get the details of company123 for the latest date which is for day=28
Thank you

Answer 1

;with cte as (select *, ROW_NUMBER() OVER (partition by CompanyName ORDER BY [Year] , [Month] , [Day] DESC) as Rn from tblMain)

select * from cte where Rn = 1