How to get first top result of each group by in SQL

I am using this SQL query, I want to get the first record of each group. I have used ROW_NUMBER() function but still its showing all records on each group. Can anyone guide me through right direction to achieve my output?

    WITH CTE_SUM AS 
(
    SELECT 
        FA.AuditIntID, FA.FileIntID, 
        MAX(FA.AuditDate) AS Auditdaa,  
        ROW_NUMBER() OVER (PARTITION BY FA.AuditIntID ORDER BY FA.AuditIntID DESC) AS rn
    FROM  
        DBO.Audit FA WITH (NOLOCK)
    WHERE
        FA.FirmIntID = 1 
    GROUP BY 
        FA.AuditIntID, FA.FileIntID
)
SELECT 
    CS.AuditIntID, MDC.EntityIntID, CS.Auditdaa,
    CS.fileintid, cs.rn 
FROM
    CTE_SUM CS WITH (NOLOCK)
INNER JOIN  
    [DBO].[Meta] MDC ON MDC.FileIntID = CS.FileIntID 
WHERE 
    rn = 1
ORDER BY
    MDC.EntityIntID,CS.Auditdaa DESC

Below is my sample table data, in table I want to retrieve only the first row of each group. eg in table you can see (fileintid= 160051) and 2nd (fileintid =320072) want see in the result and so on... which are the first row of each group as my row of result. I can see the issue every row_number is showing same value in Out Put Reults data.

Out Put Results

Expecting the below output with above query, Below its showing top 1st record of each row of each group by.

It is unclear why you are using row aggregation (GROUP BY / MAX) in your query. In your task description you don't say anything about aggregating, you just want to pick certain rows, that's all. This is what ROW_NUMBER in your query is for.

You want one result row per entityintid, so use a partition by entityintid when numbering your rows.

select entityintid, auditdate, fileintid
from
(
  select
    m.entityintid,
    cs.auditdate,
    m.fileintid,
    row_number() over (partition by m.entityintid order by cs.auditintid desc) as rn
  from [DBO].[Meta] m
  join cte_sum cs on cs.fileintid = m.fileintid
) with_rn
where rn = 1
order by entityintid;

A pattern for achieving what you want looks like this:

select <columns>
from yourtable firstrow
left join yourtable priorrow
on <join condition>
where priorrow.someidentifier is null
group by <group criterias>
order by <order criterias>

columns: These are the actual columns you are interested about

join condition: these conditions are ensuring first and foremost that firstrow and priorrow are of the same group and you need here the counter-intuitive, but useful check that priorrow is earlier than firstrow in your order. The reason for this counter-intuitive check is that you need to check for any rows whether there are earlier rows and firstrow, the actual first row is recognized by the fact that you do not find a priorrow that's earlier in your sort than firstrow. This is why you check for exceptions and you will only allow a single firstrow into the results per group, that which has no priorrow pair that would be earlier in the sort, this is why we check via the where clause that priorrow is nonexistent

group criterias: whatever criterias you need to group by

order criterias: whatever criterias you want to sort by

I would partition by EntityIntID and order by Auditdaa

    WITH cte_sum AS (
    
    SELECT 
    
    EntityIntID,
    Auditdaa,
    fileintid,
    ROW_NUMBER() OVER (PARTITION BY EntityIntID ORDER BY Auditdaa DESC) AS r
    
    FROM
    DBO.Audit
    
    )
    
    SELECT
    
    EntityIntID,
    Auditdaa,
    fileintid,
    
    FROM
    cte_sum
    
    WHERE

r=1