Need to get appropriate/expected row number in Oracle PLSQL

Getting the following output when i use the below query

SELECT P.RefNum
    ,Ian.ID
    ,Ian.Date
    ,Igl.Name
    ,Ian.Comments
    ,ROW_NUMBER() OVER (
        PARTITION BY P.RefNum
        ,I.Name ORDER BY Ian.Name
        ) AS ROWNUMBER
FROM Table1 P
INNER JOIN Table2 Igl ON P.GrpNum = Igl.GrpNum
INNER JOIN Table3 I ON Igl.Num = I.Num
INNER JOIN Table4 Ian ON Igl.Num = Ian.Num
WHERE P.RefNum = <InputParameter>

But the expected output should be as below (Refer RowNumber column)

Answer

First find “groups” each of those rows belongs to (partitioned by refnum and name) (that’s what the temp CTE does), and then apply dense_rank to fetch the final result.

Sample data (simplified, as I don’t have your tables):

SQL> WITH
  2     test (refnum,
  3           id,
  4           datecreated,
  5           name)
  6     AS
  7        (SELECT 3, 7000, DATE '2022-04-18', 'A-1' FROM DUAL
  8         UNION ALL
  9         SELECT 3, 7001, DATE '2022-04-19', 'A-1' FROM DUAL
 10         UNION ALL
 11         SELECT 3, 7002, DATE '2022-04-20', 'A-1' FROM DUAL
 12         UNION ALL
 13         SELECT 3, 7003, DATE '2022-03-29', '2-3' FROM DUAL
 14         UNION ALL
 15         SELECT 3, 7004, DATE '2022-03-30', '2-3' FROM DUAL
 16         UNION ALL
 17         SELECT 3, 7005, DATE '2022-04-11', 'L-5' FROM DUAL),

Query begins here:

 18     temp
 19     AS
 20        (SELECT t.*,
 21                id - ROW_NUMBER () OVER (PARTITION BY refnum, name ORDER BY id) grp
 22           FROM test t)
 23    SELECT t.*, DENSE_RANK () OVER (ORDER BY grp) rn
 24      FROM temp t
 25  ORDER BY refnum, name, id;

    REFNUM         ID DATECREATE NAM        GRP         RN
---------- ---------- ---------- --- ---------- ----------
         3       7000 18-04-2022 A-1       6999          1
         3       7001 19-04-2022 A-1       6999          1
         3       7002 20-04-2022 A-1       6999          1
         3       7005 11-04-2022 L-5       7004          3
         3       7003 29-03-2022 2-3       7002          2
         3       7004 30-03-2022 2-3       7002          2

6 rows selected.

SQL>