Getting the row number in specific order

Getting the following output when use the below query

SELECT Tmp.*
      ,DENSE_RANK () OVER (ORDER BY Grp) Rn
FROM (
    SELECT T.*, Id - ROW_NUMBER () OVER (PARTITION BY Recordnum, Imgname ORDER BY Id) Grp
    FROM Testtabledetails T) Tmp
ORDER BY 1, 2;

But expected output is (When RECORDNUM is changed then RN should be reset to 1 and get incremented based on IMGNAME)

Use the below SQL for data setup

CREATE TABLE TestTableDetails (
 Recordnum NUMBER
 ,Imgname VARCHAR2 (64)
 ,Fileext VARCHAR2 (8)
);

INSERT ALL 
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042726800, '5000433', '.SBT')
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042726800, '5000433', '.SBT')
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042726800, '5000436', '.SBT')
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042726800, '5000436', '.SBT')
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042726800, '5000438', '.SBT')
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042726800, '5000438', '.SBT')
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042727000, '5000439', '.SBT')
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042727000, '5000440', '.SBT')
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042727200, '5000441', '.SBT')
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042727200, '5000442', '.SBT')
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042727200, '5000442', '.SBT')
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042727201, '5000443', '.SBT')
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042727201, '5000443', '.SBT')
INTO TestTableDetails (Recordnum, Imgname, Fileext) VALUES (
10042727201, '5000444', '.SBT')
SELECT * FROM DUAL;

COMMIT;

Please assist me

CodePudding user response：

I think you made a mistake in your desired output, where rn doesn't reset on recordnum change from *200 to *201.

Other than that, it's just:

SELECT T.*,
       DENSE_RANK () OVER (PARTITION BY Recordnum ORDER BY Imgname) Rn
FROM Testtabledetails T
ORDER BY 1, 2;