Search code examples
sqloracle-databaseoracle12coracle19c

Need to get appropriate/expected row number in Oracle PLSQL

Getting the following output when i use the below query

enter image description here

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)

enter image description here

Solution

  • 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>