Search code examples
sqloraclewindow-functions

Using dense rank after sorting the records

I need some help in using the dense rank function in sql

I have the following data in the table,

enter image description here

I want to sort the records based on the CREATED DATE column and then assign the same row number if the document id is the same for multiple records. I have used the below query but the row number is not sorted

 SELECT * FROM (((SELECT * FROM (SELECT   DENSE_RANK() OVER(order by VC_VIN_DOCUMENT_ID) AS "RowNumber",
VC_VIN_DOCUMENT_ID,VC_VIN_LOCALE,VC_VIN_IMDOC_CREATE_DATE,VC_VIN_CARLINE_CODE,VC_VIN_DOCUMENT_TYPE
from (SELECT DISTINCT VC_VIN_IMDOC_CREATE_DATE,VC_VIN_DOCUMENT_TYPE, VC_VIN_LOCALE,VC_VIN_DOCUMENT_ID,VC_VIN_CARLINE_CODE from OK_DC.GMS3_VC_MME_VIN_SI_DETAIL ORDER BY VC_VIN_IMDOC_CREATE_DATE DESC)))))  ;

I want to achieve something like below.

enter image description here

Could someone please help me with the query.

Thanks for your time!!

Solution

  • You can do this in two steps. First is to get the created date. Then assign the row number:

    select vsd.*,
       dense_rank() over (order by max_cd desc, VC_VIN_DOCUMENT_ID) as rowNumber
from (select vsd.*,
             MAX(vsd.VC_VIN_IMDOC_CREATE_DATE) over (partition by vscd.VC_VIN_DOCUMENT_ID) as max_cd
      from OK_DC.GMS3_VC_MME_VIN_SI_DETAIL vsd
     ) vsd;