SQL - Using a Group By with Adding More Columns Showing Additional Rows-CodePudding

I have the following SQL statement:

SELECT H1.INCIDENT_NUMBER,
    H1.HISTORY_START_DATE,
    H1.ASSIGNED_GROUP, 
    H1.STATUS
FROM INCIDENT_HISTORY_PUBLIC as H1
WHERE H1.INCIDENT_NUMBER IN (
   SELECT INCIDENT_NUMBER
   FROM INCIDENT_HISTORY_PUBLIC
   WHERE ASSIGNED_GROUP LIKE ' DS$_%' ESCAPE '$'
)
ORDER BY H1.INCIDENT_NUMBER

Part of the results are shown as below:

What I'm trying to accomplish from here is for each INCIDENT_NUMBER, grab the MAX(HISTORY_START_DATE). I've tried using the 'Group By' but I need to keep the ASSIGNED_GROUP AND STATUS columns and when I add them back into the 'Group By,' I'm getting multiple rows again for each INCIDENT_NUMBER.

Results I am looking for:

Do I need a subquery or something? What am I missing?

CodePudding user response：

You need to use Row_Number() with Partition by like this:

SELECT * 
FROM (
SELECT H1.INCIDENT_NUMBER,
    H1.ASSIGNED_GROUP, 
    H1.HISTORY_START_DATE AS MAX_Date
    H1.STATUS,
    Row_Number() over (Partition by H1.INCIDENT_NUMBER order by H1.HISTORY_START_DATE desc) rw
FROM INCIDENT_HISTORY_PUBLIC as H1
WHERE H1.INCIDENT_NUMBER IN (
   SELECT INCIDENT_NUMBER
   FROM INCIDENT_HISTORY_PUBLIC
   WHERE ASSIGNED_GROUP LIKE ' DS$_%' ESCAPE '$'
)) t
where t.rw=1 
order by t.INCIDENT_NUMBER