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
EntityIntID | Auditdaa | fileintid | rn |
---|---|---|---|
1 | 7/28/23 12:53 | 160051 | 1 |
1 | 7/27/23 9:49 | 380075 | 1 |
1 | 6/27/23 10:06 | 310073 | 1 |
1 | 6/27/23 9:48 | 310073 | 1 |
1 | 6/27/23 9:48 | 310073 | 1 |
1 | 6/27/23 9:46 | 310073 | 1 |
2 | 7/4/23 5:42 | 320072 | 1 |
2 | 6/27/23 11:25 | 310074 | 1 |
2 | 6/27/23 11:24 | 310074 | 1 |
2 | 6/27/23 11:23 | 140050 | 1 |
2 | 6/27/23 10:43 | 310074 | 1 |
2 | 6/27/23 10:43 | 310074 | 1 |
2 | 6/27/23 10:43 | 310074 | 1 |
2 | 6/27/23 9:44 | 310072 | 1 |
2 | 6/26/23 19:15 | 300073 | 1 |
2 | 6/26/23 19:13 | 300073 | 1 |
2 | 6/26/23 19:12 | 300073 | 1 |
2 | 6/26/23 19:09 | 120036 | 1 |
2 | 6/26/23 19:09 | 300073 | 1 |
2 | 6/26/23 19:09 | 300073 | 1 |
2 | 6/26/23 19:08 | 300073 | 1 |
2 | 6/26/23 19:08 | 120036 | 1 |
Expecting the below output with above query, Below its showing top 1st record of each row of each group by.
EntityIntID | Auditdaa | fileintid | rn |
---|---|---|---|
1 | 7/28/23 12:53 | 160051 | 1 |
2 | 7/4/23 5:42 | 320072 | 1 |
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;