Find MIX and MAX of Row_Number() of Analytic function?

Question

I am further taking reference from : Apply OFFSET and LIMIT in ORACLE for complex Join Queries?.

How to find out the MIN and MAX value of rn here ?

SELECT q.*
FROM (SELECT DEPT.ID rowobjid,
   DEPT.CREATOR createdby,
   DEPT.CREATE_DATE createddate,
   DEPT.UPDATED_BY updatedby,
   DEPT.LAST_UPDATE_DATE updateddate,
   DEPT.NAME name,
   DEPT.STATUS status,
   statusT.DESCR statusdesc,
   REL.ROWID_DEPT1 rowidDEPT1,
   REL.ROWID_DEPT2 rowidDEPT2,
   DEPT2.DEPT_FROM_VAL parentcid,
   DEPT2.NAME parentname,
   ROW_NUMBER() OVER (PARTITION BY DEPT.CREATE_DATE ORDER BY DEPT.ID) AS rn
FROM TEST.DEPT_TABLE DEPT
LEFT JOIN TEST.STATUS_TABLE statusT
ON DEPT.STATUS = statusT.STATUS
LEFT JOIN TEST.C_REL_DEPT rel
ON DEPT.ID = REL.ROWID_DEPT2
LEFT JOIN TEST.DEPT_TABLE DEPT2
ON REL.ROWID_DEPT1 = DEPT2.ID) q

Popeye · Accepted Answer · 2020-06-23T07:53:16.373

Min value of rn will always be 1 and for finding the max. Use max after * as follows:

SELECT q.*, max(rn) over () as max_rn, 1 as min_rn
FROM (SELECT 
.......
.......

If you want max rn within inner query for each partition then you can use count analytical function as it will return total number of records within the defined partition(which is nothing but the max rn for that partition) as follows:

COUNT(1) OVER (PARTITION BY DEPT.CREATE_DATE) AS MAX_RN, 1 AS MIN_RN

Find MIX and MAX of Row_Number() of Analytic function?

1 Answers1